    
     Home

Basic SQL Guide.
SQL Introduction.
What is Table?
SQL Create Table.
SQL Insert Statement.
SQL Select Keyword.
SQL Order by clause.
SQL Count Statement.
SQL Group By Clause.
SQL Having Clause.
SQL Alias Tables.
SQL Join Tables.
SQL Outer Join.
SQL Update Statement.
SQL Delete Statement.
SQL Distinct Keyword.
SQL Where Keyword.
SQL And Or Keyword.
SQL In Keyword.
SQL Between Keyword.
SQL Like Keyword.
SQL Functions.
SQL Concatenate.
SQL Substrings.
SQL Trim Function.
SQL Constraints.
SQL Primary Key.
SQL Foreign Key.
SQL Cerate Views.
SQL Create Index.
SQL Alter Table.
SQL Drop Table.
SQL Truncate Table.
SQL Summary.

SQL Outer Join.

Back Next

Previously in SQL Tutorial, we had looked at left SQL join, or inner SQL join, where we select rows common to the participating tables to a join. What about the cases where we are interested in selecting elements in a table regardless of whether they are present in the second table? We will now need to use the SQL OUTER JOIN command.

The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle database, we will place an "(+)" in the WHERE clause on the other side of the table for which we want to include all the rows.

Let's assume that we have the following two tables to experiment with SQL Outer Join,

Table: Employees

FirstName	LastName	Email	DOB	Phone
Austin	Hennery	austin.it@gamail.com	24/11/1978	446 102-2222
Creston	Narvon	narvon_crest@hotmail.com	22/04/1976	443 325-4545
Kate	Bladen	kbladen@usa.net	01/05/1980	326 503-3232
Angela	Julie	julieee@yahoo.co.uk	11/12/1977	326 323-8888
Bobbi	Ferguson	bobi123@kiwibox.com	10/02/1970	506 252-9875
Cute	Alfa	cafa@gmail.com	19/05/1972	506 272-4566
Manager	Co.	manager.co@company.com	19/05/19711	326 323-8148

Table Employees_Salary

FirstName	Monthh	Salary
Austin	January	1000$
Austin	February	1100$
Creston	February	1100$
Kate	February	1100$
Angela	February	1000$
Bobbi	February	1200$
Cute	February	1300$

and we want to find out the Salary amount for all of the Employees. If we do a regular join, we will not be able to get what we want because we will have missed "Manager," since it does not appear in the Employees_Salary table. Therefore, we need to perform an SQL outer join on the two tables above:

Example of SQL Outer Join

SELECT emp.FirstName Employee, SUM(empSal.Salary) Salary
FROM Employees_Salary empSal, Employees emp
WHERE emp.FirstName = empSal.FirstName (+)
GROUP BY empSal.FirstName

Note that in this case, we are using the Oracle database syntax for SQL outer join.

Result of SQL Outer Join Example in Oracle:

Employee	Salary
Austin	2100$
Creston	1100$
Kate	1100$
Angela	1000$
Bobbi	1200$
Cute	1300$
Manager

Note: NULL is returned when there is no match on the second table. In this case, "Manager" does not appear in the table Employees_Salary, thus its corresponding "Salary" column is NULL (empty.). Hope this SQL Tutorial section of SQL Outer Join is enough to grip for beginners.

Back Next

HTML Rendering of above code:

[ About ] [ Contact ] [ Home ]
[ Links ] [ Site Map ] [ Services ] [ Privacy ]