When you use SQL JOIN/INNER JOIN, the result are rows that only matches in both table based on Join condition you specified in the FROM clause. If you need to display all rows from at least one of the tables mentioned in the FROM clause, you can use SQL OUTER JOIN statement. There are 3 types of SQL Outer Join. Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:
LEFT JOIN or LEFT OUTER JOIN : Left Outer Join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
RIGHT JOIN or RIGHT OUTER JOIN : A right outer join will return All rows from the right table and Null values are returned when there are no matching row in the left table.
FULL JOIN or FULL OUTER JOIN : A full outer join returns all rows in both the left and right tables. Null values given for any rows that has no match in both tables.
For example you have the following tables and data in your database:
Data in the Vendors table:
VendorID VendorName
--------- ------------------------------
V0001 Microsoft Corporation
V0002 Oracle Corporation
V0003 MySQL AB
V0004 PremiumSoft CyberTech Ltd.
V0005 Mentat Technologies
V0006 FastReport Inc.
V0007 CoreLab
Data in the Products table:
ProductID VendorID Description
---------- --------- -----------------------------------------
SDB0001 V0001 MS SQL Server Database 2005
SDB0002 V0002 Oracle Database 10g Enterprise
SDB0003 V0003 MySQL 5.0 Enterprise Edition
SDBA0001 V0004 Navicat For MySQL Administration
SDBA0002 V0005 Dreamcoder 4.1 For MySQL Enterprise
SDBA0003 V0005 Dreamcoder 4.1 For MySQL Profesional
SDBA0004 V0005 Dreamcoder 4.1 For MS SQL Server Database
SDBA0005 V0008 PostgreSQL Maestro
SDBA0006 V0008 Firebird Maestro
SDBA0007 V0008 SQLite Maestro
You noticed that Vendor V0008 doesn't exist in the Poducts table and V0006, V0007 doesn't exist in the Vendors Table. This rows will not be displayed if you use a regular SQL JOIN/INNER JOIN (where Vendors.VendorID=Products.VendorID). How to display all rows from the Vendors table although it doesnt exist on the Products table or the reverse?.
Left Join or Left Outer Join Example:
The Following SQL Statement will display all rows from the Vendors table and will display only rows from the Products table that match to the Vendors table based on VendorID field.
select Vendors.VendorID,products.ProductID,products.Description
from vendors LEFT JOIN products
on vendors.vendorid=products.vendorid
Query Result:
VendorID ProductID Description
--------- ---------- -----------------------------------------
V0001 SDB0001 MS SQL Server Database 2005
V0002 SDB0002 Oracle Database 10g Enterprise
V0003 SDB0003 MySQL 5.0 Enterprise Edition
V0004 SDBA0001 Navicat For MySQL Administration
V0005 SDBA0002 Dreamcoder 4.1 For MySQL Enterprise
V0005 SDBA0003 Dreamcoder 4.1 For MySQL Profesional
V0005 SDBA0004 Dreamcoder 4.1 For MS SQL Server Database
V0006 NULL NULL
V0007 NULL NULL
Right Join or Right Outer Join Example::
The Following SQL Statement will display all rows from the Products table and display only rows from the Vendors table that match to the Products table based on VendorID field.
select Vendors.VendorID,products.ProductID,products.Description
from vendors RIGHT JOIN products
on vendors.vendorid=products.vendorid
Query Result:
VendorID ProductID Description
--------- ---------- -----------------------------------------
V0001 SDB0001 MS SQL Server Database 2005
V0002 SDB0002 Oracle Database 10g Enterprise
V0003 SDB0003 MySQL 5.0 Enterprise Edition
V0004 SDBA0001 Navicat For MySQL Administration
V0005 SDBA0002 Dreamcoder 4.1 For MySQL Enterprise
V0005 SDBA0003 Dreamcoder 4.1 For MySQL Profesional
V0005 SDBA0004 Dreamcoder 4.1 For MS SQL Server Database
NULL SDBA0005 PostgreSQL Maestro
NULL SDBA0006 Firebird Maestro
NULL SDBA0007 SQLite Maestro
Full Join or Full Outer Join Example::
The Following SQL Statement will display all rows from both Vendors and Products table.
select Vendors.VendorID,products.ProductID,products.Description
from vendors FULL JOIN products
on vendors.vendorid=products.vendorid
Query Result:
VendorID ProductID Description
--------- ---------- -----------------------------------------
V0001 SDB0001 MS SQL Server Database 2005
V0002 SDB0002 Oracle Database 10g Enterprise
V0003 SDB0003 MySQL 5.0 Enterprise Edition
V0004 SDBA0001 Navicat For MySQL Administration
V0005 SDBA0002 Dreamcoder 4.1 For MySQL Enterprise
V0005 SDBA0003 Dreamcoder 4.1 For MySQL Profesional
V0005 SDBA0004 Dreamcoder 4.1 For MS SQL Server Database
NULL SDBA0005 PostgreSQL Maestro
NULL SDBA0006 Firebird Maestro
NULL SDBA0007 SQLite Maestro
0 comments:
Post a Comment