SQL Outer Join, SQL Tutorial, Global Guide Line Technology.
Forum | Blog |


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 Create Views.           
 SQL Create Index.           
 SQL Alter Table.             
 SQL Drop Table.             
 SQL Truncate Table.      
 SQL Summary.                

SQL Tutorial >> 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$

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     

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

Copyright 2005 -  2021 www.globalguideline.com All rights reserved. (Best viewed in IE 6.0+ or Firefox 2.0+ at 1024 * 768 or higher) 442 visitors are online now