SQL LEFT JOIN

SQL LEFT JOIN query returns all records from the LEFT TABLE (table_1), and the all matching record from the RIGHT TABLE (table_2), even if there are no matches in the right table;

That means if the ON clause matches zero records in the right table; then the JOIN will still return a row in the result, but with NULL in each column from the right table.

LEFT JOIN Syntax

SELECT column_name(s)
FROM table_1
LEFT JOIN table_2
ON table_1.column_name = table_2.column_name;

Example With Two Tables

CUSTOMERS Table

Simple Customers record table & the name of the table is CUSTOMERS. The Column(s) name of the table like [ID, NAME, EMAIL, ADDRESS, and SALARY]:

ID NAME EMAIL ADDRESS SALARY
1 Mark JK mark@gmail.com Mumbai 5000
2 Nitika nitika@gmail.com Delhi 6700
3 Suneel Kumar kumars@gmail.com Pune 9400
4 Jenifer jenifer@gmail.com Indore 3400
5 McKinley mckinley@gmail.com UK 7200
6 Deepika deepika@gmail.com Nashik 3600

ORDERS Table

This is a sample ORDER table; The columns of the table are [OID, DATE, CUSTOMER_ID, and AMOUNT]:

OID DATE CUSTOMER_ID AMOUNT
210 12/01/2019 2 4000
209 11/22/2019 4 2000
211 12/10/2019 1 1000
212 12/17/2019 6 3000

Mapping Image Of LEFT JOIN

JOIN Both Table Using LEFT JOIN

SELECT ID, NAME, DATE, AMOUNT
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Result Look Like

ID NAME DATE AMOUNT
1 Mark JK 12/10/2019 1000
2 Nitika 12/01/2019 4000
3 Suneel Kumar NULL NULL
4 Jenifer 11/22/2019 2000
5 McKinley NULL NULL
6 Deepika 12/17/2019 3000

SQL LEFT OUTER JOIN

A LEFT OUTER JOIN used to return a result set that contains all rows in the LEFT TABLE (table_1) that do not exist in the RIGHT TABLE (table_2).

Basically it means, LEFT OUTER JOIN used to preserves the unmatched rows from LEFT TABLE, JOIN with a NULL in the RIGHT TABLE.

Mapping Image Of LEFT OUTER JOIN

LEFT OUTER JOIN Syntax

SELECT *
FROM table_1
LEFT [OUTER] JOIN table_2
ON table_1.column_name = table_2.column_name;

Policy

GDATAMART.COM is optimized for learning & job information; The users try to improve your knowledge and practical experience through this site but we cannot warrant full correctness of all content... Learn More → | Sitemap →

© 2020 GDATAMART | All Rights Reserved