Sql Left Join Clause By Examples, Understanding My

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.

Đang xem: Sql left join

Left Join: Syntax

SELECT *FROM table1LEFT < OUTER > JOIN table2ON table1.column_name=table2.column_name;Pictorial representation:

*

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:

SELECT company.company_id,company.company_name,company.company_city,foods.company_id,foods.item_nameFROM companyLEFT JOIN foodsON 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 Mix15 Jack Hill Ltd London 15 Cheez-It15 Jack Hill Ltd London 15 BN Biscuit17 Foodies. London 17 Mighty Munch15 Jack Hill Ltd London 15 Pot Rice18 Order All Boston 18 Jaffa Cakes19 sip-n-Bite. New YorkPictorial Presentation of the above example:

READ:  1001+ Những Câu Nói Hay Về Cuộc Sống Buồn Giúp Bạn Vực Dậy Mạnh Mẽ
*

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 600Pictorial Presentation:

*

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_idWHERE c.company_name IS NOT NULLORDER 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.

READ:  Bae Có Nghĩa Là Gì ? Nghĩa Của Bae Trên Facebook Và Trong Các Lĩnh Vực Khác

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 625Pictorial Presentation:

*

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 Boston15 Jack Hill Ltd London16 Akas Foods Delhi17 Foodies. London19 sip-n-Bite. New YorkSample table: foods

ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_ID——– ————————- ———- ———-1 Chex Mix Pcs 166 Cheez-It Pcs 152 BN Biscuit Pcs 153 Mighty Munch Pcs 174 Pot Rice Pcs 155 Jaffa Cakes Pcs 187 Salt n Shake PcsThe 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.

Xem thêm: Acid Salicylic Acid Là Gì ? Cách Dùng Và Bảo Quản Axit Salicylic

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”.

READ:  Máy Cnc Là Gì ? Đứng Máy Cnc Là Gì

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 1615 Jack Hill Ltd 6 Cheez-It 1515 Jack Hill Ltd 2 BN Biscuit 1517 Foodies. 3 Mighty Munch 1715 Jack Hill Ltd 4 Pot Rice 1518 Order All 5 Jaffa Cakes 1819 sip-n-Bite. NULL NULL NULLOutputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Xem thêm: Giới Thiệu Thông Tin Blogger: Viết Mã Bug ), Viết Mã Bug

LEFT JOIN: Relational Databases

Key points to remember :

Click on the following to get the slides presentation –

*

Practice SQL Exercises

SQL JOINSSQL SUBQUERIESMovie DatabaseSoccer DatabaseHospital DatabaseEmployee DatabaseMore to come!

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

Xem thêm bài viết thuộc chuyên mục: tin tổng hợp

Bài viết hay nhất