Which of the following join contains only rows from left table which match the joining condition without having any column from the right table?

Last update on August 19 2022 21:51:36 (UTC/GMT +8 hours)

LEFT JOIN

The SQL LEFT JOIN (specified with the keywords LEFT JOIN and ON) joins two tables and fetches all matching rows of two tables for which the SQL-expression is true, plus rows from the frist table that do not match any row in the second table.

Pictorial presentation of SQL Left Join:

Which of the following join contains only rows from left table which match the joining condition without having any column from the right table?

Left Join: Syntax

SELECT * FROM table1 LEFT [ OUTER ] JOIN table2 ON table1.column_name=table2.column_name;

SQL LEFT join fetches a complete set of records from table1, with the matching records (depending on the availability) in table2. The result is NULL in the right side when no matching will take place.

Syntax diagram - LEFT JOIN

Which of the following join contains only rows from left table which match the joining condition without having any column from the right table?

Example of SQL Left Join

To get company name and company id columns from company table and company id, item name, item unit columns from foods table, after an OUTER JOINING with these mentioned tables, the following SQL statement can be used :

Sample table: foods

Sample table: company

SQL Code:

SELECT company.company_id,company.company_name, company.company_city,foods.company_id,foods.item_name FROM company LEFT JOIN foods ON company.company_id = foods.company_id;

Explanation:

This SQL statement would return all rows from the company table and only those rows from the foods table where the joined fields are equal and if the ON clause matches no records in the 'foods' table, the join will still return rows, but the NULL in each column of the right table.

Output:

COMPANY_ID COMPANY_NAME COMPANY_CITY COMPANY_ID ITEM_NAME ---------- ------------------------- ------------------------- ---------- -------------- 16 Akas Foods Delhi 16 Chex Mix 15 Jack Hill Ltd London 15 Cheez-It 15 Jack Hill Ltd London 15 BN Biscuit 17 Foodies. London 17 Mighty Munch 15 Jack Hill Ltd London 15 Pot Rice 18 Order All Boston 18 Jaffa Cakes 19 sip-n-Bite. New York

Pictorial Presentation of the above example SQL Left Join:

Which of the following join contains only rows from left table which match the joining condition without having any column from the right table?

Example of SQL Left Join using multiple columns

To filtered out those bill number, item name and the bill amount for each bill which bill amount exceeds the value 500 and must be available at the food stall, the following SQL statement can be used :

Sample table: foods

Sample table: counter_sale

SQL Code:

SELECT a.bill_no, b.item_name, a.bill_amt FROM counter_sale a LEFT JOIN foods b ON a.item_id=b.item_id WHERE a.bill_amt>500;

Explanation:

This SQL statement will first join all rows from the counter_sale table and only those rows from the foods table where the joined fields are equal and if the ON clause matches no records in the foods table, the join will still return rows, but the NULL in each column of right table, therefore eliminates those rows which bill amount is less than or equal to 500.

Output:

BILL_NO ITEM_NAME BILL_AMT ---------- ------------------------- ---------- 1002 Chex Mix 2000 1006 Mighty Munch 625 1001 Pot Rice 600 1004 Pot Rice 540 1005 Salt n Shake 600

Pictorial Presentation of SQL Left Join using Multiple Columns:

Which of the following join contains only rows from left table which match the joining condition without having any column from the right table?

Example of SQL Left Join using multiple tables

To filtered out those bill number, item name, company name and city and the bill amount for each bill, which items are available in foods table, and their manufacturer must have enlisted to supply that item, and no NULL value for manufacturer are not allowed, the following SQL statement can be used:

Sample table: foods

Sample table: company

Sample table: counter_sale

SQL Code:

SELECT a.bill_no, b.item_name,c.company_name, c.company_city, a.bill_amt FROM counter_sale a LEFT JOIN foods b ON a.item_id=b.item_id LEFT JOIN company c ON b.company_id=c.company_id WHERE c.company_name IS NOT NULL ORDER BY a.bill_no;

Explanation:

This SQL statement will first join all rows from the counter_sale table and only those rows from the foods table where the joined fields are matching and if the ON clause matches no records in the foods table, the join will still return rows, but the NULL in each column of the right table. Therefore this result will join with company table and all rows from result table and matched and unmatched rows from company table will also come, but for the unmatched rows of company table, the column value will be NULL. Therefore the WHERE clause will eliminate those rows which company name column value is NULL and after that, the ORDER BY clause will arrange the rows in ascending order according to the bill number.

Output:

BILL_NO ITEM_NAME COMPANY_NAME COMPANY_CITY BILL_AMT ---------- ------------------------- ------------------------- ------------------------- ---------- 1001 Pot Rice Jack Hill Ltd London 600 1002 Chex Mix Akas Foods Delhi 2000 1003 Cheez-It Jack Hill Ltd London 300 1004 Pot Rice Jack Hill Ltd London 540 1006 Mighty Munch Foodies. London 625

Pictorial Presentation of SQL Left Join using Multiple Tables:

Which of the following join contains only rows from left table which match the joining condition without having any column from the right table?

What is the difference between Left Join and Left Outer Join in SQL?

There is actually no difference between a left join and a left outer join – both of them refer to the similar operation in SQL.

Sample table: company

COMPANY_ID COMPANY_NAME COMPANY_CITY ---------- ------------------------- ------------- 18 Order All Boston 15 Jack Hill Ltd London 16 Akas Foods Delhi 17 Foodies. London 19 sip-n-Bite. New York

Sample table: foods

ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_ID -------- ------------------------- ---------- ---------- 1 Chex Mix Pcs 16 6 Cheez-It Pcs 15 2 BN Biscuit Pcs 15 3 Mighty Munch Pcs 17 4 Pot Rice Pcs 15 5 Jaffa Cakes Pcs 18 7 Salt n Shake Pcs

The important point to be noted that the very last row in the company table, the company ID does not exist in the foods table. Also, the very last row in the foods table the value of company ID is NULL and does not exist in the company table. These facts will prove to be significant of the left join.

Here the SQL statement without using "outer" with "left join".

SQL Code:

SELECT company.company_id,company.company_name, foods.item_id, foods.item_name, foods.company_id FROM company LEFT JOIN foods ON company.company_id = foods.company_id;

Running the SQL with the "outer" keyword, would give us the exact same results as running the SQL without the “outer”. Here the SQL statement with "outer" with "left join".

SQL Code:

SELECT company.company_id,company.company_name, foods.item_id, foods.item_name, foods.company_id FROM company LEFT OUTER JOIN foods ON company.company_id = foods.company_id;

A left outer join or left join retains all of the rows of the left table company, regardless of whether there is a row that matches on the right table foods. Here is the output below for both of the above statement.

Output:

COMPANY_ID COMPANY_NAME ITEM_ID ITEM_NAME COMPANY_ID ---------- ------------------------- -------- ------------------------- ---------- 16 Akas Foods 1 Chex Mix 16 15 Jack Hill Ltd 6 Cheez-It 15 15 Jack Hill Ltd 2 BN Biscuit 15 17 Foodies. 3 Mighty Munch 17 15 Jack Hill Ltd 4 Pot Rice 15 18 Order All 5 Jaffa Cakes 18 19 sip-n-Bite. NULL NULL NULL

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

LEFT JOIN: Relational Databases

  • Oracle LEFT JOIN
  • MySQL LEFT JOIN
  • PostgreSQL LEFT JOIN
  • SQLite LEFT JOIN

Key points to remember :

Click on the following to get the slides presentation -

Which of the following join contains only rows from left table which match the joining condition without having any column from the right table?

Practice SQL Exercises

  • SQL Exercises, Practice, Solution
  • SQL Retrieve data from tables [33 Exercises]
  • SQL Boolean and Relational operators [12 Exercises]
  • SQL Wildcard and Special operators [22 Exercises]
  • SQL Aggregate Functions [25 Exercises]
  • SQL Formatting query output [10 Exercises]
  • SQL Quering on Multiple Tables [8 Exercises]
  • FILTERING and SORTING on HR Database [38 Exercises]
  • SQL JOINS
    • SQL JOINS [29 Exercises]
    • SQL JOINS on HR Database [27 Exercises]
  • SQL SUBQUERIES
    • SQL SUBQUERIES [39 Exercises]
    • SQL SUBQUERIES on HR Database [55 Exercises]
  • SQL Union[9 Exercises]
  • SQL View[16 Exercises]
  • SQL User Account Management [16 Exercise]
  • Movie Database
    • BASIC queries on movie Database [10 Exercises]
    • SUBQUERIES on movie Database [16 Exercises]
    • JOINS on movie Database [24 Exercises]
  • Soccer Database
    • Introduction
    • BASIC queries on soccer Database [29 Exercises]
    • SUBQUERIES on soccer Database [33 Exercises]
    • JOINS queries on soccer Database [61 Exercises]
  • Hospital Database
    • Introduction
    • BASIC, SUBQUERIES, and JOINS [39 Exercises]
  • Employee Database
    • BASIC queries on employee Database [115 Exercises]
    • SUBQUERIES on employee Database [77 Exercises]
  • More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: SQL OUTER JOIN
Next: SQL RIGHT JOIN

Which of the following joins contains only rows from left table which match the joining condition without having any columns from the right table?

Introduction to SQL Server LEFT JOIN clause The LEFT JOIN clause allows you to query data from multiple tables. The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no matching rows are found in the right table, NULL are used.

Which join returns all the rows from the right table with the matching rows in the left table?

SQL cross join It generates all the rows from the left table which is then combined with all the rows from the right table. This type of join is also known as a Cartesian product(A*B).

Which type of join returns rows from two table only if there is a match between columns in both table?

Outer joins are joins that return matched values and unmatched values from either or both tables. There are a few types of outer joins: LEFT JOIN returns only unmatched rows from the left table, as well as matched rows in both tables.

Which of the following join returns the records that match the values for the join columns from both the tables on the join?

INNER JOIN This type of join returns those records which have matching values in both tables.