Thursday, February 26, 2009

SQL RIGHT JOIN

As you have learned about the SQL LEFT JOIN where all the rows are returned from the left table ever when there is no match from the right table, the same case is with the SQL RIGHT JOIN, but here all the rows from the right side table is returned without matching the left side table. Let start with the syntax:

Syntax:-
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

Now look at the two tables, TABLE A and TABLE B:

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 we apply the SQL RIGHT JOIN query:

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

The above query will return all the rows from TABLE A even there is no match with TABLE B. That does not mean that no check for common rows are made. The result of the above query will be:

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


347641

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