SQL What is left join | SQL left join Performance with example

In this article, you will learn What is SQL LEFT JOIN clause, and How to use it to query data from multiple tables with example. The SQL LEFT JOIN clause returns all records from the left table (T1) and matching the records from the right table (T2). The result is NULL from the right side if there is no matching found.

LEFT JOIN the command allows you to query data from multiple tables. it will returns all the rows from the left table (T1) and matched rows from the right table (T2) if there are no matched rows found in the right table (T2), then NULL are used.

.
Important Note:
The left join clause returns all the values from the left table and plus matched values from the right table otherwise returns NULL in case of no matching join predicate.
LEFT JOIN clause and LEFT OUTER JOIN clause are the same.

sql left join

SQL LEFT JOIN syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

OR

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

there are the following illustrates, how to join two tables (T1 and T2) using the SQL LEFT JOIN clause:

Explanation of syntax

therefore, in the above syntax, T1 and T2 indicate the left and right tables respectively, for each row from the left T1 table, first, the above query compares it with all the rows from the right T2 table. furthermore, if a pair of rows causes the join predicate to evaluate to TRUE, the column values from these rows will be combined to form a new row which then included in the result set.

furthermore, if a row from the left table (T1) does not have any matched row from the table (T2), then the query combines column values of the row from the left table with NULL for each column values from the (T2) right table.
In a simple way, we can say that the LEFT JOIN clause returns all rows from T1 (left table) and matching rows or NULL values from the T2 (right table).

SQL Demonstratio Database:-

there are the following below is a selection from the "Students" table:

StudentID StudentName Address ContactNumber City PostalCode Country
1 John Carry street no-241, Florida road +01-23459567 New york 23416 United States
2 Devid Poll New campus, flat-44 arihayat +01-67854275 Berlin 78456 Germany
3 Dyna Michel 212, charlies road new city +01-38323407 Florida 19432 United States
4 Charlies Bryed Flat no-12, marvo street +01-21790374 New jersey 43298 United States

And a selection from the "Admission" table:

AdmissionID StudentID EmployeeID AdmissionDate JoinID
20411 3 9 2012-06-11 4
20412 21 9 2012-06-11 1
20413 27 4 2012-06-11 3
20414 32 7 2012-06-11 2

SQL LEFT JOIN Example

The following SQLstatement will select all students, and any admission they might have:

.
Note:
The LEFT JOIN clause returns all records from the left table (Students), even if there are no matches in the right table (Admission)

The following illustrates the LEFT JOIN of two tables T1(A, B, C) and Table T2(1,2,3). therefore, the LEFT JOIN will match rows from the Table (T1) with the rows from the table (T2) using color patterns:
sql left join clause example
In the given above color pattern illustration, no row from the table (T2) matches the row 1 from the table (T1) therefore, NULL is used. Rows 2 and 3 from the T1 table match with rows 1 and 2 from the table (T2) respectively.

Example 1: SQL Left Join using multiple columns

Question: To filtered out those bill numbers, item names, and the bill amount for each bill which bill amount exceeds the value 1000 and must be available at the food stall.
Therefore, there are the following SQL statement can be used :

Demo Table: goods

ItemID ItemName ItemUnit CompanyID
1 Multigrains Packet 11
5 Aashirvaad Atta Packet 13
2 Honey Packet 15
4 Ground Nut Packet 11
3 Mustard Oil Packet 13

Demo Table: CounterSale

BillNo ItemID SaleQTY SaleRate BillAmount
101 4 15 50 750
103 2 25 60 1500
104 5 30 20 600
105 3 20 40 800
106 1 35 40 1400

SQL Code:

Explanation:

Therefore, as you see in the given SQL statement will first join all rows from the CounterSale table and only those rows from the Goods table where the joined fields are equal and if the ON clause matches no records in the Goods 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 1000.

Example 2: SQL Left Join clause using multiple tables

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

Demo Table: goods

ItemID ItemName ItemUnit CompanyID
1 Multigrains Packet 11
5 Aashirvaad Atta Packet 13
2 Honey Packet 15
4 Ground Nut Packet 11
3 Mustard Oil Packet 13

Demo Table: Company

CompanyID ComapanyName CompanyCity
13 Patanjali Dehradun
15 Aashirvad Madhya Pradesh
11 Dabar Mumbai
13 Britania Delhi

Demo Table: CounterSale

BillNo ItemID SaleQTY SaleRate BillAmount
101 4 15 50 750
103 2 25 60 1500
104 5 30 20 600
105 3 20 40 800
106 1 35 40 1400

SQL Code:

Explanation:

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

About Author

admin