SQL Left Join
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:
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
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:
-- Selecting specific columns from the 'company' table and the 'foods' table
SELECT company.company_id, company.company_name, company.company_city, foods.company_id, foods.item_name
-- Joining the 'company' table with the 'foods' table using a LEFT JOIN
FROM company
LEFT JOIN foods
-- Matching rows from 'company' and 'foods' where the company_id values are equal
ON company.company_id = foods.company_id;
Explanation:
- This SQL query is retrieving data from two tables: 'company' and 'foods'.
- It selects specific columns from these tables: 'company_id', 'company_name', 'company_city' from the 'company' table, and 'company_id', 'item_name' from the 'foods' table.
- The query uses a LEFT JOIN to combine rows from the 'company' table with matching rows from the 'foods' table.
- The join condition is specified in the ON clause, which matches rows based on the equality of 'company_id' values between the two tables.
- If there are no matching rows in the 'foods' table for a particular row in the 'company' table, NULL values will be returned for the columns selected from the 'foods' table.
- This query is useful for retrieving information about companies and the food items they produce, even if some companies don't produce any food items (hence the LEFT JOIN to ensure all companies are included in the result).
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:
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:
-- Selecting specific columns from the 'counter_sale' table and the 'foods' table
SELECT a.bill_no, b.item_name, a.bill_amt
-- Joining the 'counter_sale' table with the 'foods' table using a LEFT JOIN
FROM counter_sale a
LEFT JOIN foods b
-- Matching rows from 'counter_sale' and 'foods' where the item_id values are equal
ON a.item_id=b.item_id
-- Filtering the result to include only rows where the bill_amt is greater than 500
WHERE a.bill_amt > 500;
Explanation:
- This SQL query is retrieving data from two tables: 'counter_sale' and 'foods'.
- It selects specific columns from these tables: 'bill_no' and 'bill_amt' from the 'counter_sale' table, and 'item_name' from the 'foods' table.
- The query uses a LEFT JOIN to combine rows from the 'counter_sale' table with matching rows from the 'foods' table.
- The join condition is specified in the ON clause, which matches rows based on the equality of 'item_id' values between the two tables.
- If there are no matching rows in the 'foods' table for a particular row in the 'counter_sale' table, NULL values will be returned for the 'item_name'.
- The WHERE clause filters the result to include only rows where the 'bill_amt' is greater than 500.
- This query is useful for retrieving information about counter sales where the bill amount exceeds a certain threshold, along with the names of the food items purchased (if available).
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:
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:
-- Selecting specific columns from the 'counter_sale' table, the 'foods' table, and the 'company' table
SELECT a.bill_no, b.item_name, c.company_name, c.company_city, a.bill_amt
-- Joining the 'counter_sale' table with the 'foods' table using a LEFT JOIN
FROM counter_sale a
LEFT JOIN foods b ON a.item_id = b.item_id
-- Joining the result of the previous join with the 'company' table using a LEFT JOIN
LEFT JOIN company c ON b.company_id = c.company_id
-- Filtering the result to include only rows where the 'company_name' is not NULL
WHERE c.company_name IS NOT NULL
-- Sorting the result by the 'bill_no' column in ascending order
ORDER BY a.bill_no;
Explanation:
- This SQL query is retrieving data from three tables: 'counter_sale', 'foods', and 'company'.
- It selects specific columns from these tables: 'bill_no' from the 'counter_sale' table, 'item_name' from the 'foods' table, 'company_name' and 'company_city' from the 'company' table, and 'bill_amt' from the 'counter_sale' table.
- The query uses LEFT JOINs to combine rows from the 'counter_sale' table with matching rows from the 'foods' table, and then with matching rows from the 'company' table.
- The join conditions are specified in the ON clauses, which match rows based on the equality of 'item_id' between 'counter_sale' and 'foods', and 'company_id' between 'foods' and 'company'.
- If there are no matching rows in the 'foods' or 'company' tables for a particular row in the 'counter_sale' or 'foods' tables, NULL values will be returned for the respective columns.
- The WHERE clause filters the result to include only rows where the 'company_name' is not NULL, ensuring that only counter sales with associated companies are included.
- The ORDER BY clause sorts the result by the 'bill_no' column in ascending order.
- This query is useful for retrieving information about counter sales, including the associated food items and the companies that produce them, while ensuring that only valid data is included in the result.
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:
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:
-- Selecting specific columns from the 'company' table and the 'foods' table
SELECT company.company_id, company.company_name, foods.item_id, foods.item_name, foods.company_id
-- Joining the 'company' table with the 'foods' table using a LEFT JOIN
FROM company
LEFT JOIN foods
-- Matching rows from 'company' and 'foods' where the company_id values are equal
ON company.company_id = foods.company_id;
Explanation:
- This SQL query is retrieving data from two tables: 'company' and 'foods'.
- It selects specific columns from these tables: 'company_id' and 'company_name' from the 'company' table, and 'item_id', 'item_name', and 'company_id' from the 'foods' table.
- The query uses a LEFT JOIN to combine rows from the 'company' table with matching rows from the 'foods' table.
- The join condition is specified in the ON clause, which matches rows based on the equality of 'company_id' values between the two tables.
- If there are no matching rows in the 'foods' table for a particular row in the 'company' table, NULL values will be returned for the columns selected from the 'foods' table.
- This query is useful for retrieving information about companies and the food items they produce, ensuring that all companies are included in the result regardless of whether they produce any food items (hence the LEFT JOIN).
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:
-- Selecting specific columns from the 'company' table and the 'foods' table
SELECT company.company_id, company.company_name, foods.item_id, foods.item_name, foods.company_id
-- Joining the 'company' table with the 'foods' table using a LEFT OUTER JOIN
FROM company
LEFT OUTER JOIN foods
-- Matching rows from 'company' and 'foods' where the company_id values are equal
ON company.company_id = foods.company_id;
Explanation:
- This SQL query is retrieving data from two tables: 'company' and 'foods'.
- It selects specific columns from these tables: 'company_id' and 'company_name' from the 'company' table, and 'item_id', 'item_name', and 'company_id' from the 'foods' table.
- The query uses a LEFT OUTER JOIN to combine rows from the 'company' table with matching rows from the 'foods' table.
- The join condition is specified in the ON clause, which matches rows based on the equality of 'company_id' values between the two tables.
- If there are no matching rows in the 'foods' table for a particular row in the 'company' table, NULL values will be returned for the columns selected from the 'foods' table.
- This query is useful for retrieving information about companies and the food items they produce, ensuring that all companies are included in the result regardless of whether they produce any food items (hence the LEFT OUTER JOIN, which behaves the same as LEFT JOIN).
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
Key points to remember :
Click on the following to get the slides presentation -
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: SQL OUTER JOIN
Next: SQL RIGHT JOIN
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics