SQL LEFT JOIN or LEFT OUTER JOIN returns all rows from the left table and the matching rows from the right table.
If a row in the left table does not have any matching row in the right table then columns of the right table will have nulls value.
Syntax
SELECT COLUMN(S),... FROM TABLE T1 LEFT OUTER JOIN TABLE T2 ON T1.COLUMN = T2. COLUMN WHERE CONDITIONS..
Lets look at an example of LEFT OUTER JOIN in SQL Server.
Here we have two tables Department and Emoloyee_Info.
CREATE TABLE dbo.DEPARTMENT (DEPT_ID INT PRIMARY KEY, DEPT_NAME VARCHAR(50), DEPT_CODE VARCHAR(15)) INSERT INTO dbo.DEPARTMENT(DEPT_ID, DEPT_NAME, DEPT_CODE) VALUES(501, 'IT', '001'), (502, 'FINANCE', '002'), (503, 'HR', '003'), (504, 'MIS', '004') CREATE TABLE dbo.EMPLOYEE_INFO ( EMP_ID INT PRIMARY KEY, EMP_NAME VARCHAR(50), EMP_CODE VARCHAR(15), DEPT_ID INT REFERENCES dbo.DEPARTMENT(DEPT_ID) ) INSERT INTO dbo.EMPLOYEE_INFO (EMP_ID, EMP_NAME, EMP_CODE, DEPT_ID ) VALUES (1, 'MANISH SINGH', 'EN-0034', 501), (2, 'MANOJ RAWAT', 'EN-0035', 501), (3, 'RAJESH KUMAWAT', 'EN-0036', 501), (4, 'SURAJ K', 'EN-0037', 502), (5, 'RUPESH SINGH', 'EN-0038', 502), (6, 'ARJUN PAL', 'EN-0039', 503), (7, 'GURPREET KAUR', 'EN-0040', 504), (8, 'TRILOK KUMAR', 'EN-0041', 504), (9, 'RANUK KHAN', 'EN-0041', null), (10, 'MANDEEP GREWAL', 'EN-0041', null)
BASIC USE OF LEFT OUTER JOIN
Suppose you want to see all the employees basic details along with the department name and code, if present in department table.
Means you need to get the details of those employees who have matching DEPT_ID in DEPARTMENT table as well as who does not have matching DEPT_ID in DEPARTMENT table, in this case DEPT_NAME, DEPT_CODE values will be null.
Here you can not use SQL INNER JOIN , As it returns only those records for which column have matching rows in both table and eliminates non matching rows.
So to get the records for all the employees for matching and non matching DEPT_ID of EMPLOYEE_INFO table with DEPT_ID in DEPARTMENT table you can use LEFT OUTER JOIN.
Following statement use LEFT OUTER JOIN clause to join both tables and returns all the employee details from EMPLOYEE_INFO table without eliminating non matching rows with DEPARMENT table, and Null values is given to non matching rows in DEPARMENT table.
Generally, all the records are returned from the table that will be on the left side of LEFT OUTER JOIN clause, and null value are returned for records of right side table, in case of non match rows.
SELECT EMP.EMP_ID, EMP.EMP_NAME, EMP.EMP_CODE, DEPT.DEPT_NAME, DEPT.DEPT_CODE FROM dbo.EMPLOYEE_INFO EMP LEFT OUTER JOIN dbo.DEPARTMENT DEPT ON EMP.DEPT_ID = DEPT.DEPT_ID
As you can see, DEPT_NAME and DEPT_CODEE values are null for last two records in result set becaxuse they do not have matching DEPT_ID values in DEPARTMENT table, so it returns their complete details from EMPLOYEE_INFO table while gives null values for DEPARTMENT details (DEPT_NAME and DEPT_CODE ).
Also Read..
1,254 total views, 2 views today