Tuesday, February 24, 2009

SQL LEFT JOIN

This article expect that you have a prior knowledge of JOINING the table.

Before we start let start with the syntax of SQL LEFT JOIN, because this will help to understand the rest of the article quite easily

SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

The major part of the above syntax is " LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name;"
Notice the keyword LEFT JOIN , what does this mean ? LEFT JOIN returns all the rows from left table even though the rows in the right table do not exist. To explain it clearly lets take an example:

TABLE A

P_Id LastName FirstName Address City
1 ABC
DEF
Street 10
City 1
2 GHI JKL
Street 20
City 2
3 MNO
PQR
Street 30 City 3


TABLE B

O_Id OrderNo P_Id
1 778951 3
2 446781 3
3 224561 1
4 245621 1
5 347641 15

Now if we join two tables on the above provided Syntax, i.e.

SELECT TABLE A.LastName, TABLE A.FirstName, TABLE B.OrderNo
FROM TABLE A
LEFT JOIN Orders
ON TABLE A.P_Id=TABLE B.P_Id
ORDER BY TABLE A.LastName ;

Yes, you guessed it correctly that all the rows from the TABLE A will be returned without taking into consideration that those rows exits in the TABLE B

The OUTPUT will be

LastName FirstName OrderNo
ABC DEF 224561
ABC DEF 245621
MNO PQR 778951
MNO PQR 446781


347641


NOTE: In some places SQL LEFT JOIN is also refered as LEFT OUTER JOIN.

0 comments: