gplivna.eu

SQL join types

1 Introduction

Joins are one of the basic constructions of SQL and Databases as such - they combine records from two or more database tables into one row source, one set of rows with the same columns. And these columns can originate from either of the joined tables as well as be formed using expressions and built-in or user-defined functions. Depending on join type and join restrictions returned row count can be from 0 till all possible combinations of involved tables. So if one has two tables containing 10 000 rows each then the maximum number of resultant rows can be 10 000 * 10 000 = 100 000 000 rows. Databases are built to make joins as efficient as possible. It means almost always joining data in database is more efficient than doing that somewhere else. It also means one has to know the power and possibilities of joins to fully exploit their strength. And it doesn't matter whether one is using Oracle, SQL Server, MySQL, IBM DB2, PostgreSQL or whatever else DBMS.
NB 1! It is worth to mention that throughout all this article it is supposed two tables are joined together. It is used both for theoretical parts of it as well as examples. However one has to remember that every two row sources can be joined together, and both or one of them can be table, view, subquery, materialized view or other construction returning rows.
NB 2! All examples are created for Oracle database and written according to Oracle syntax. However it doesn't matter what database management system is used, many of them with (probably) very little modifications or even exactly the same can be used for every other DBMS supporting joins. Exactly why they work or why not are described for Oracle, SQL Server and MySQL. If you need to use them for other DBMSes then you should check these examples yourself although I would be very pleased if you'd send me information what examples are not working on what DBMSes. I will include this info here along with your name.

Contents

1 Introduction
2 Classification schemes
   2.1 Depending on condition existance
   2.2 Depending on row selection
   2.3 Depending on comparison operator
   2.4 Depending on used tables
   2.5 Classification schemes summary
3 Used data model for examples
4 Cross joins
   4.1 Syntax
   4.2 Facts to remember
   4.3 Examples
5 Inner joins
   5.1 Syntax
   5.2 Facts to remember
   5.3 Examples
   5.4 Alternative syntax
6 Outer joins
   6.1 Syntax
   6.2 Facts to remember
   6.3 Examples
   6.4 Difference between predicates in ON clause vs predicates in WHERE clause
   6.5 Old style Oracle syntax for Outer joins
   6.6 Old style SQL Server syntax for Outer joins
7 Equi join
   7.1 Examples
8 Non-Equi (Theta) join
   8.1 Facts to remember
   8.2 Examples
9 Self join
   9.1 Examples
10 Natural join
   10.1 Syntax
   10.2 Facts to remember
   10.3 Examples
11 Named columns join
   11.1 Syntax
   11.2 Facts to remember
   11.3 Examples
12 Example usage for various DBMSes
   12.1 Oracle
   12.2 Microsoft SQL Server
   12.3 MySQL
13 References and more information

2 Classification schemes

There are different classification schemes and different criteria according to what joins are classified. As a result there is a bit mess in the process of understanding them. I haven't found a nice scheme or even textual description how each one of various classification schemes and join types relates to other. In the following Meta model there is one possible variant of that, however theoreticians probably would break lances around other possible classification schemes.
SQL join types

2.1 Depending on condition existence

Depending on whether we add any join condition or not there are following join types (yellow in model):
  • CROSS JOIN (synonyms also CARTESIAN JOIN, CARTESIAN PRODUCT) - there isn't any join condition or it is always true. All other join types degrade to CROSS JOINS as soon as join condition (-s) is (are) always true.
  • JOIN WITH RESTRICTION - there is applied join condition to joined tables. One can write join with restriction using different syntactic notations. As already said above every join with restriction may degrade to cross join.
  • NATURAL JOIN - syntactic notation joining source tables on all columns having the same name. This can be quite dangerous as explained below in the chapter for Natural join. Natural joins always are Equi joins.
  • QUALIFIED JOIN - user has possibility to define which columns are used in join condition.
  • NAMED COLUMNS JOIN - syntactic notation joining source tables on user defined columns having the same name. This is less dangerous than Natural join and just short form of writing Equi joins on some common columns joined together. Named columns joins always are Equi joins.
  • CONDITIONAL JOIN - fully controllable syntax by user. This is the most widespread and most useful syntactic convention. Depending on used predicates in join condition it may be Equi join as well as Non-equi join.

2.2 Depending on row selection

Depending on whether only rows satisfying join condition are selected or all rows are selected in one or both involved tables, joins are divided into (green in model):
  • INNER JOIN - only rows satisfying selection criteria from both joined tables are selected.
  • LEFT OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from left joined table are being kept along with Nulls instead of actual right joined table values.
  • RIGHT OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from right joined table are being kept along with Nulls instead of actual left joined table values.
  • FULL OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows both from left joined table and right joined table are being kept along with Nulls instead of values from other table.

2.3 Depending on comparison operator

Depending on used comparison operator in join condition there are following join types (blue in model):
  • PREDICATE OPERATOR TYPE - based on predicate operator type (i.e. equality and everything other) joins are divided into two parts Equi joins and Nonequi joins.
  • EQUI JOIN - join condition uses only equality predicate "=". It can be both explicit for Conditional join and implicit for Natural join and Named columns join.
  • THETA (NONEQUI) JOIN - everything other than equality predicate "=", for example ">=", between. This can be used only by Conditional joins.

2.4 Depending on used tables

There is one special case for classification based on what tables are involved in join (orange in model).
  • SELF JOIN - table is joined to itself.

2.5 Classification schemes summary

There are some interesting facts we can observe from the diagram above:
  • Every join either is Cartesian join or can degrade to Cartesian join in case the join criteria always are true. Each and every join type, be it inner, outer, self, natural, equi, non-equi, named column or join with explicitly declared join condition can degrade to Cartesian join.
  • Classification scheme Depending on row existence (i.e. inner and outer joins) are absolutely independent of other classification schemes, e.g. natural joins can be both inner or outer, as well as self join can be inner or outer.
  • Every other join type can be self join if both joined tables are the same.
  • Equi join - although most used one - is not the only one. It is the only one if natural join or named columns join syntax is used, but for explicitly specified join condition other comparison operators besides equality ("=") can be used.

3 Used data model for examples

Throughout this entire article we will use following tables (table names according to my favourite naming conventions) and table data:
CREATE TABLE addresses (                                             
  adr_id INTEGER NOT NULL PRIMARY KEY,                               
  adr_city VARCHAR(15),                                              
  adr_country VARCHAR(15) NOT NULL);                                 
CREATE TABLE persons (                                               
  prs_id INTEGER NOT NULL PRIMARY KEY,                               
  prs_father_id INTEGER,                                             
  prs_mother_id INTEGER,                                             
  prs_adr_id INTEGER,                                                
  prs_first_name VARCHAR(15),                                        
  prs_surname VARCHAR(15),                                           
  CONSTRAINT prs_prs_father_fk FOREIGN KEY (prs_father_id)           
    REFERENCES persons(prs_id),                                      
  CONSTRAINT prs_prs_mother_fk FOREIGN KEY (prs_mother_id)           
    REFERENCES persons(prs_id),                                      
  CONSTRAINT prs_adr_fk FOREIGN KEY (prs_adr_id)                     
    REFERENCES addresses(adr_id));                                   
INSERT INTO addresses VALUES (1, 'RIGA', 'LATVIA');                  
INSERT INTO addresses VALUES (2, 'BERLIN', 'GERMANY');               
INSERT INTO addresses VALUES (3, 'NEW YORK', 'USA');                 
INSERT INTO persons VALUES (1, NULL, NULL, NULL, 'JANIS', 'BERZINS');
INSERT INTO persons VALUES (2, 1, NULL, 2, 'PETER', 'BERZINS');      
INSERT INTO persons VALUES (3, NULL, NULL, 2, 'ANN', 'SMYTH');       
INSERT INTO persons VALUES (4, 2, 3, 2, 'CHARLES', 'BERZINS');       
COMMIT;                                                              
Although data model is very simple, it is very important for you to understand the data for examples, because all the examples are as important as theory. So the data model is as follows:
Example data model
And data looks as follows:

SQL> SELECT * FROM persons;                                             
                                                                        
PRS_ID PRS_FATHER_ID PRS_MOTHER_ID PRS_ADR_ID PRS_FIRST_NAME PRS_SURNAME
------ ------------- ------------- ---------- -------------- -----------
     1                                        JANIS          BERZINS    
     2             1                        2 PETER          BERZINS    
     3                                      2 ANN            SMYTH      
     4             2             3          2 CHARLES        BERZINS    
SQL> SELECT * FROM addresses;         
                                      
    ADR_ID ADR_CITY        ADR_COUNTRY
---------- --------------- -----------
         1 RIGA            LATVIA     
         2 BERLIN          GERMANY    
         3 NEW YORK        USA        

4 Cross joins

Cross join consists of all possible combinations of two table's rows. Imagine we have two tables TableA and TableB with 4 and 3 rows respectively as follows:
Table data examples
Cross join of both tables is as follows:
SQL cross (Cartesian) join
It is obvious that cross join of rather small tables may lead to quite disastrous resultant record count, for example, two tables of just 100 thousand rows each results in 100 000 * 100 000 = 10 000 000 000 (10 billion rows).

4.1 Syntax

There are two syntax types how to normally get cross join. One explicitly declares cross join:
SELECT <column list>           
FROM <left joined table>       
CROSS JOIN <right joined table>
Second syntax type is functionally the same; just both tables are written in FROM clause:
SELECT <column list>                          
FROM <left joined table>, <right joined table>
As one can see, second query hasn't WHERE clause, it means as soon as one omits it, and uses more than one table in FROM clause, it results in cross join and possibly very many rows.

4.2 Facts to remember

Usually cross joins are used quite rarely; some of the scenarios could be as follows:
  • Possibility to generate high amount of rows. As we can see from relatively small tables there is possibility to get quite monstrous numbers.
  • Find out all possible row combinations of some tables. Mostly this is useful for reports where one needs to generate all combinations for example all nationalities x genders for persons.
  • To join a table with just one row. Most often used to get some configuration parameters.
There are other facts to remember about cross joins:
  • Every other join type can degrade to cross join in case the join condition is always true for all records. This is true for natural joins, inner joins, outer joins etc. See Example 3, Example 4 and Example 5.
  • Making cross join as Self join one must use table aliases to let know database from which table instance get selected columns. See Example 6 and Example 7.

4.3 Examples

Example 1 Selects first name, surname and city from Cartesian join of persons and addresses using CROSS JOIN syntax. The result is identical as in Example 2.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
CROSS JOIN addresses;                       
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
JANIS           BERZINS         RIGA        
PETER           BERZINS         RIGA        
ANN             SMYTH           RIGA        
CHARLES         BERZINS         RIGA        
JANIS           BERZINS         BERLIN      
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      
JANIS           BERZINS         NEW YORK    
PETER           BERZINS         NEW YORK    
ANN             SMYTH           NEW YORK    
CHARLES         BERZINS         NEW YORK    
                                            
12 rows selected.                           

Example 2 Selects first name, surname and city from Cartesian join of persons and addresses using both table names in FROM clause. The result is identical as in Example 1.

SELECT prs_first_name, prs_surname, adr_city
FROM persons, addresses;                    
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
JANIS           BERZINS         RIGA        
PETER           BERZINS         RIGA        
ANN             SMYTH           RIGA        
CHARLES         BERZINS         RIGA        
JANIS           BERZINS         BERLIN      
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      
JANIS           BERZINS         NEW YORK    
PETER           BERZINS         NEW YORK    
ANN             SMYTH           NEW YORK    
CHARLES         BERZINS         NEW YORK    
                                            
12 rows selected.                           

Example 3 Selects first name, surname and city from persons and addresses using NATURAL INNER JOIN syntax. Because both tables haven't column with the same name, result is identical as in Example 1 and Example 2.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
NATURAL INNER JOIN addresses;               

Example 4 Selects first name, surname and city from persons and addresses using NATURAL FULL OUTER JOIN syntax. Because both tables haven't column with the same name, result is identical as in Example 1, Example 2 and Example 3, just row order is different.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
NATURAL FULL OUTER JOIN addresses;          
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
JANIS           BERZINS         RIGA        
JANIS           BERZINS         BERLIN      
JANIS           BERZINS         NEW YORK    
PETER           BERZINS         RIGA        
PETER           BERZINS         BERLIN      
PETER           BERZINS         NEW YORK    
ANN             SMYTH           RIGA        
ANN             SMYTH           BERLIN      
ANN             SMYTH           NEW YORK    
CHARLES         BERZINS         RIGA        
CHARLES         BERZINS         BERLIN      
CHARLES         BERZINS         NEW YORK    

Example 5 Selects first name, surname and city from persons and addresses using simply INNER JOIN. Because join condition 1=1 is always true result is Cartesian join.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
INNNER JOIN addresses ON (1=1);             
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
JANIS           BERZINS         RIGA        
PETER           BERZINS         RIGA        
ANN             SMYTH           RIGA        
CHARLES         BERZINS         RIGA        
JANIS           BERZINS         BERLIN      
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      
JANIS           BERZINS         NEW YORK    
PETER           BERZINS         NEW YORK    
ANN             SMYTH           NEW YORK    
CHARLES         BERZINS         NEW YORK    
                                            
12 rows selected.                           

Example 6 Selects all columns from persons using SELF JOIN to the same table. Because there are the same column name aliases must be used otherwise error is raised as in this example.

SELECT *                             
FROM persons                         
CROSS JOIN persons;                  
SELECT *                             
*                                    
ERROR at line 1:                     
ORA-00918: column ambiguously defined

Example 7 Selects first name and surname from persons using SELF JOIN to the same table. Because there are the same column name aliases must be used. There are 4 rows in persons table so we got 4*4 = 16 rows from Cartesian product.

SELECT p1.prs_first_name, p2.prs_surname
FROM persons p1                         
CROSS JOIN persons p2;                  
                                        
PRS_FIRST_NAME  PRS_SURNAME             
--------------- ---------------         
JANIS           BERZINS                 
PETER           BERZINS                 
ANN             BERZINS                 
CHARLES         BERZINS                 
JANIS           BERZINS                 
PETER           BERZINS                 
ANN             BERZINS                 
CHARLES         BERZINS                 
JANIS           SMYTH                   
PETER           SMYTH                   
ANN             SMYTH                   
CHARLES         SMYTH                   
JANIS           BERZINS                 
PETER           BERZINS                 
ANN             BERZINS                 
CHARLES         BERZINS                 
                                        
16 rows selected.                       

5 Inner joins

Inner join is the most widely used join type. It returns those and only those rows from both joined tables satisfying join condition. Usually join condition is equality of two columns one from table A and other from table B. Sometimes it is expanded so that more than one column of table A must be equal to table's B column. Although these are most common join conditions, and in fact equality is not the only one.
Assuming we have tables A and B as defined in chapter about Cross joins inner join with equality can be visually interpreted as follows: SQL Inner join
Blue rectangle shows common columns from both tables satisfying join criteria. As one can see generally not all rows are returned.

5.1 Syntax

Inner join syntax is as follows:
SELECT <column list>             
FROM <left joined table>         
[INNER] JOIN <right joined table>
ON <join condition>              
Keyword INNER sometimes is avoided, but anyway it remains inner join. After the keyword ON join condition is written, generally it can contain many predicates connected with Boolean AND, OR, NOT.

5.2 Facts to remember

All following facts are relevant to almost every join type, however I'd like to stress them here because inner join is the most common join type and as a result more impact will be done taking them into account.
  • Databases are designed to do joins efficiently. Not client, not middle tier, but databases. Creating data model usually normalization is being done mostly to avoid data duplication. To show data in person-readable form, joining is one of the necessary prerequisites. Inner join is one of the most common join types and it should be done in database. Database means efficiently in the place where data resides and not in client/middle tier cycling through (possibly) many cycles.
  • It should be noted that other clauses for SQL statements e.g. GROUP BY, HAVING, ORDER BY are of course usable for SQL statements with joins.
  • One must be cautious using join conditions with columns without NOT NULL constraint. Comparing NULL values with different explicit values or even Nulls is probably a bit counter-intuitive on the first sight.
  • The position of additional conditions is irrelevant for INNER joins. These can be written both as join condition and in WHERE clause, result is the same. See Example 11 and Example 12.
  • For Inner join it is not relevant, which table is first one and which second one. See Example 10.

5.3 Examples

Example 8 Selects first name, surname and city for every person.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
INNER JOIN addresses ON prs_adr_id = adr_id;
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      

Example 9 Selects first name, surname and city for every person. Keyword Inner is not present, but functionally result is the same.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
JOIN addresses ON prs_adr_id = adr_id;      
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      

Example 10 Inner join doesn't care, which table is the first, which the second one. Compare with Example 8.

SELECT prs_first_name, prs_surname, adr_city
FROM addresses                              
JOIN persons ON prs_adr_id = adr_id;        
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      
The position of additional conditions is irrelevant for INNER joins. These can be written both as join condition and in WHERE clause, result is the same.

Example 11 Selects first name, surname and city for every person, which first name starts with letter P. Additional condition is placed as join condition. Result is the same as in Example 12.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
INNER JOIN addresses ON prs_adr_id = adr_id 
  AND prs_first_name LIKE 'P%';             
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
PETER           BERZINS         BERLIN      

Example 12 Selects first name, surname and city for every person, which first name starts with letter P. Additional condition is placed in WHERE clause. Result is the same as in Example 11.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
INNER JOIN addresses ON prs_adr_id = adr_id 
WHERE prs_first_name LIKE 'P%';             
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
PETER           BERZINS         BERLIN      
Following example illustrate the use of OR operator in join condition. From this example is obvious that inner joins are subset of Cartesian joins - for person with name starting with P Cartesian join is performed.

Example 13 One should be cautious using logical operator OR in join conditions, this can lead to quite interesting results. Compare the result with Example 11 and Example 1.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
INNER JOIN addresses ON prs_adr_id = adr_id 
  OR prs_first_name LIKE 'P%';              
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
PETER           BERZINS         RIGA        
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      
PETER           BERZINS         NEW YORK    

Example 14 If join condition is always true inner join degrades back to cross join. This is the same Example 5.

Next example shows that not always join condition must be equality (=). One can create another join conditions for example join between persons and addresses where identifiers are different. See also the fact that rows from persons where prs_adr_id is NULL is not selected, because every compare with NULL always gives false.

Example 15 Example of inner join which is not equi join, i.e. join condition is not equality.

SELECT prs_first_name, prs_surname, adr_city, prs_adr_id, adr_id     
FROM persons                                                         
INNER JOIN addresses ON prs_adr_id <> adr_id;                        
                                                                     
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY        PRS_ADR_ID     ADR_ID
--------------- --------------- --------------- ---------- ----------
PETER           BERZINS         RIGA                     2          1
ANN             SMYTH           RIGA                     2          1
CHARLES         BERZINS         RIGA                     2          1
PETER           BERZINS         NEW YORK                 2          3
ANN             SMYTH           NEW YORK                 2          3
CHARLES         BERZINS         NEW YORK                 2          3
Of course tables involved in inner join can be the same, i.e. inner join can be also self join.

Example 16 Example of table join to itself (self join) - two times table is joined to itself, as a result there are three instances of the same table in this SQL query.

SELECT father.prs_first_name "Father Name",                      
       mother.prs_first_name "Mother Name",                      
       child.prs_first_name "Child Name"                         
FROM persons child                                               
INNER JOIN persons father ON child.prs_father_id = father.prs_id 
INNER JOIN persons mother ON child.prs_mother_id = mother.prs_id;
                                                                 
Father Name     Mother Name     Child Name                       
--------------- --------------- ---------------                  
PETER           ANN             CHARLES                          

5.4 Alternative syntax

For this join type one can use also different syntax:
SELECT <column list>                          
FROM <left joined table>, <right joined table>
WHERE <join condition>                        
In this syntax all join conditions are written together with other conditions in WHERE clause. Every join written in INNER JOIN syntax, can be rewritten with this syntax and vice versa. Functionally result is the same, i.e. returned rows are the same. Time after time people have quite hot discussion which syntax is better for example "should one use ANSI join syntax when writing an Oracle application? (http://www.freelists.org/archives/oracle-l/10-2006/threads.html#00594)". On the one hand for this syntax it is easier to get Cartesian join on the other hand for "explicit inner join syntax". I personally can write both syntaxes although slightly prefer this alternative syntax simply because more than 10 years ago started to write joins in this manner :

Example 17 This is equivalent to Example 8. Both tables are mentioned in FROM clause and join conditions are in WHERE clause.

SELECT prs_first_name, prs_surname, adr_city
FROM persons, addresses                     
WHERE prs_adr_id = adr_id;                  
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      

Example 18 This is equivalent to Example 16. All three tables are mentioned in FROM clause and both join conditions are in WHERE clause.

SELECT father.prs_first_name "Father Name",       
       mother.prs_first_name "Mother Name",       
       child.prs_first_name "Child Name"          
FROM persons child, persons father, persons mother
WHERE child.prs_father_id = father.prs_id         
  AND child.prs_mother_id = mother.prs_id;        
                                                  
Father Name     Mother Name     Child Name        
--------------- --------------- ---------------   
PETER           ANN             CHARLES           

6 Outer joins

Outer join extends the functionality of inner join. It returns following rows:
  • the same rows as inner join i.e. rows from both tables, which matches join condition and
  • rows from one or both tables, which do not match join condition along with NULL values in place of other table's columns.
There are three types of outer join - Left, Right and Full outer join. Left join keeps rows in left joined table. Right join keeps rows in right joined table. Full join keeps rows in both tables. Let's imagine we have the same tables A and B as defined in chapter about Cross joins. Then each of left, right and full join can be graphically shown as follows:
SQL Left outer join
SQL Right outer join
SQL Full outer join
Rectangle in the pictures above represents result of inner join. So we can see that left outer join keeps all rows from left joined table, as much as possible combines with the rows from right table, and those, which cannot be joined, leaves with Nulls on the right side. Right outer join does the same, just table A and B are switched. Full outer join tries to combine as much rows as possible and then rows from left joined table, which cannot be joined, leaves with Nulls on the right side and rows from right joined table, which cannot be joined, leaves with Nulls on the left side.

6.1 Syntax

Outer join syntax is as follows:
SELECT <column list>                             
FROM <left joined table>                         
LEFT|RIGHT|FULL [OUTER] JOIN <right joined table>
ON <join condition>                              
Only one of the keywords left, right, full can be provided but exactly one is required. Keyword OUTER sometimes is avoided, but anyway keywords left, right or full indicate it is outer join. After the keyword ON join condition is written, generally it can contain many predicates connected with Boolean AND, OR, NOT.

6.2 Facts to remember

  • Outer joins should be used only when necessary. If it is possible (data model and business data allows) inner join should be used. Inner join offers greater flexibility for optimizer and doesn't mislead people to the thoughts that some rows of one cannot be joined to other table.
  • Unlike Inner joins it is important where the condition is written - either as join condition or in WHERE clause. See chapter Difference between predicates in ON clause vs. predicates in WHERE clause.
  • Not all Database management systems support Full outer joins. But one can use set operator Union of Left and Right joins to simulate Full outer join. See Example 22a.

6.3 Examples

Example 19 Selects first name, surname and city for every person keeping all persons, even without address. Compare with Example 8.

SELECT prs_first_name, prs_surname, adr_city     
FROM persons                                     
LEFT OUTER JOIN addresses ON prs_adr_id = adr_id;
                                                 
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY         
--------------- --------------- ---------------  
JANIS           BERZINS                          
PETER           BERZINS         BERLIN           
ANN             SMYTH           BERLIN           
CHARLES         BERZINS         BERLIN           

Example 20 Selects first name, surname and city for every person. Keyword outer is not present, but functionally result is the same as in Example 19.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
LEFT JOIN addresses ON prs_adr_id = adr_id; 
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
JANIS           BERZINS                     
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      

Example 21 Selects first name, surname and city for every person keeping all addresses, even without any associated person. Compare with Example 19.

SELECT prs_first_name, prs_surname, adr_city      
FROM persons                                      
RIGHT OUTER JOIN addresses ON prs_adr_id = adr_id;
                                                  
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY          
--------------- --------------- ---------------   
                                RIGA              
PETER           BERZINS         BERLIN            
CHARLES         BERZINS         BERLIN            
ANN             SMYTH           BERLIN            
                                NEW YORK          

Example 22 Selects first name, surname and city for every person keeping all personas and addresses. Compare with Example 19 and Example 21.

SELECT prs_first_name, prs_surname, adr_city     
FROM persons                                     
FULL OUTER JOIN addresses ON prs_adr_id = adr_id;
                                                 
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY         
--------------- --------------- ---------------  
JANIS           BERZINS                          
PETER           BERZINS         BERLIN           
ANN             SMYTH           BERLIN           
CHARLES         BERZINS         BERLIN           
                                RIGA             
                                NEW YORK         
Some databases for example MySQL doesn't support full outer join syntax directly. Then one must use workaround to get desired output. The same effect can be achieved with
  • left join unioned with
  • right join without rows resulting from inner join.
Of course one can combine left and right joins as well as change the order of joined tables to create more substitutes, but the main idea is the same.

Example 22a Emulates FULL outer join using left and right join with Union. Compare with Example 22.

SELECT prs_first_name, prs_surname, adr_city     
FROM persons                                     
LEFT OUTER JOIN addresses ON prs_adr_id = adr_id 
UNION ALL                                        
SELECT prs_first_name, prs_surname, adr_city     
FROM persons                                     
RIGHT OUTER JOIN addresses ON prs_adr_id = adr_id
WHERE prs_adr_id IS NULL;                        
                                                 
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY         
--------------- --------------- ---------------  
JANIS           BERZINS                          
PETER           BERZINS         BERLIN           
ANN             SMYTH           BERLIN           
CHARLES         BERZINS         BERLIN           
                                RIGA             
                                NEW YORK         

Example 23 If the condition is always true outer join degrades to cross join.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
RIGHT OUTER JOIN addresses ON 1 = 1;        
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
JANIS           BERZINS         RIGA        
PETER           BERZINS         RIGA        
ANN             SMYTH           RIGA        
CHARLES         BERZINS         RIGA        
JANIS           BERZINS         BERLIN      
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      
JANIS           BERZINS         NEW YORK    
PETER           BERZINS         NEW YORK    
ANN             SMYTH           NEW YORK    
CHARLES         BERZINS         NEW YORK    
                                            
12 rows selected.                           
Next example shows that not always join condition must be equality (=). One can create another join conditions for example join between persons and addresses where identifiers are different. There is one additional row which (in bold) is the result of outer join.

Example 24 Example of outer join which is not equi join, i.e. join condition is not equality. Compare with Example 15.

SELECT prs_first_name, prs_surname, adr_city, prs_adr_id, adr_id     
FROM persons                                                         
LEFT JOIN addresses ON prs_adr_id <> adr_id;                         
                                                                     
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY        PRS_ADR_ID     ADR_ID
--------------- --------------- --------------- ---------- ----------
JANIS           BERZINS                                              
PETER           BERZINS         RIGA                     2          1
PETER           BERZINS         NEW YORK                 2          3
ANN             SMYTH           RIGA                     2          1
ANN             SMYTH           NEW YORK                 2          3
CHARLES         BERZINS         RIGA                     2          1
CHARLES         BERZINS         NEW YORK                 2          3
In inner join we had Example 16 of three way self join to get child, his mother and father. It returned only one row. With outer joins we can get more information, also partial information. Look at the rows in bold which are added because of inner join replacement by left join.

Example 25 Example of table outer join to itself (self join) - two times table is joined to itself, as a result there are three instances of the same table in this SQL query. Compare with Example 16.

SELECT father.prs_first_name "Father Name",                     
       mother.prs_first_name "Mother Name",                     
       child.prs_first_name "Child Name"                        
FROM persons child                                              
LEFT JOIN persons father ON child.prs_father_id = father.prs_id 
LEFT JOIN persons mother ON child.prs_mother_id = mother.prs_id;
                                                                
Father Name     Mother Name     Child Name                      
--------------- --------------- ---------------                 
                                JANIS                           
JANIS                           PETER                           
                                ANN                             
PETER           ANN             CHARLES                         

6.4 Difference between predicates in ON clause vs. predicates in WHERE clause

For inner joins it was irrelevant where the additional conditions are written - both join condition and conditions in WHERE clause worked the same way - see Example 11 and Example 12. For outer joins it is important where one places additional conditions.
Let's imagine we'd like to get only persons having surname "Berzins" along with cities where they live. One has to add another condition in WHERE clause in Example 19.

Example 26 Selects first name, surname and city for every person keeping all persons, even without address. There is additional condition in WHERE clause for table persons.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
LEFT JOIN addresses ON prs_adr_id = adr_id  
WHERE prs_surname = 'BERZINS';              
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
JANIS           BERZINS                     
PETER           BERZINS         BERLIN      
CHARLES         BERZINS         BERLIN      
As expected Ann Smyth is lost from result, because she has different surname. Now let's put condition as join condition.

Example 27 Selects first name, surname and city for every person keeping all persons, even without address. There is additional condition in join clause for table persons.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
LEFT JOIN addresses ON prs_adr_id = adr_id  
  AND prs_surname = 'BERZINS';              
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
JANIS           BERZINS                     
PETER           BERZINS         BERLIN      
ANN             SMYTH                       
CHARLES         BERZINS         BERLIN      
Ann is back! Just she hasn't address. Why?
Let's look closer at mechanism how outer join result theoretically is created!
1. Firstly Cartesian join of involved table's rows is created. This is another reason why Cartesian join concept is important.
2. For each row in Cartesian join result join conditions are being checked.
2.1. If join condition is evaluated to TRUE, then this row is preserved.
2.2. If join condition is evaluated to FALSE, then this row also is preserved, with following changes:
2.2.1. Columns from the table, for which rows should be kept (left joined table persons in the example above), are preserved without changes.
2.2.2. Columns from the table, for which rows should not be kept (table addresses in the example above), are changed to NULL.
3. Only unique rows are preserved from all the rows created by step 2.2.
4. Finally WHERE clause is evaluated and preserved only these rows, which satisfy WHERE condition (-s).
So the answer why Ann was back in result set can be found in step 2.2 and its sub steps. Row is preserved along with columns from table persons, but columns from table addresses are set to NULL.
So with already known experience let's look in two more examples where additional conditions will be put on table addresses. In the following example none of the rows satisfies condition in WHERE clause, and therefore all of them are being discarded from result set according to step 4 in the algorithm above.

Example 28 Selects first name, surname and city for every person keeping all persons, even without address. There is additional condition in WHERE clause for table addresses.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
LEFT JOIN addresses ON prs_adr_id = adr_id  
WHERE adr_city = 'RIGA';                    
                                            
no rows selected                            
In the next example none of the rows from Cartesian join satisfies join condition but rows are preserved anyway, just columns from tables addresses are set to NULL according to step 2.2 in the algorithm above.

Example 29 Selects first name, surname and city for every person keeping all persons, even without address. There is additional condition in join clause for table addresses.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
LEFT JOIN addresses ON prs_adr_id = adr_id  
  AND adr_city = 'RIGA';                    
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
JANIS           BERZINS                     
PETER           BERZINS                     
ANN             SMYTH                       
CHARLES         BERZINS                     

6.5 Old style Oracle syntax for Outer joins

Even before ANSI standardized outer joins Oracle had a way to express them. Of course they have to pay for being one of the first ones, old syntax is not according to standard and has less features. However it is working and one can use it. The idea is the same as for alternative inner join syntax, just a special outer join operator (+) have to be applied for conditions for outer joined table. So the overall syntax is:
SELECT <column list>                          
FROM <left joined table>, <right joined table>
WHERE <join condition>                        
Let's look at example which is functionally equivalent to left outer join in Example 19.

Example 30 This is equivalent to Example 19, table addresses is outer joined to table persons using Oracle outer join operator (+).

SELECT prs_first_name, prs_surname, adr_city
FROM persons, addresses                     
WHERE prs_adr_id = adr_id (+);              
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
JANIS           BERZINS                     
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      
In order to get right outer join we just move outer join operator to other side of equivalence

Example 31 This is equivalent to Example 20, table persons is outer joined to table addresses using Oracle outer join operator (+).

SELECT prs_first_name, prs_surname, adr_city
FROM persons, addresses                     
WHERE prs_adr_id (+) = adr_id;              
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
                                RIGA        
PETER           BERZINS         BERLIN      
CHARLES         BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
                                NEW YORK    
Oracle outer join operator has more restrictions than more modern ANSI syntax, for example:
  • It doesn't support Full Outer join;
  • It cannot be used together with modern syntax;
  • Join condition cannot contain logical OR and IN operators.
Other common mistakes used in old style Oracle outer joins one can see in article Common errors seen when using OUTER-JOIN.

6.6 Old style SQL Server syntax for Outer joins

SQL Server has created his own syntax for outer joins just as Oracle. And it similarly has special operator. For SQL Server it was *= and =* instead of simply = in join conditions.

Example 32 This is equivalent to Example 19, table addresses is outer joined to table persons using SQL Server outer join operator *=.

SELECT prs_first_name, prs_surname, adr_city
FROM persons, addresses                     
WHERE prs_adr_id *= adr_id;                 
                                            
JANIS   BERZINS NULL                        
PETER   BERZINS BERLIN                      
ANN     SMYTH   BERLIN                      
CHARLES BERZINS BERLIN                      
In order to get right outer join we just put the * sign on the other side of equivalence =

Example 33 This is equivalent to Example 20, table persons is outer joined to table addresses using SQL Server outer join operator =*

SELECT prs_first_name, prs_surname, adr_city
FROM persons, addresses                     
WHERE prs_adr_id =*adr_id;                  
                                            
NULL    NULL    RIGA                        
PETER   BERZINS BERLIN                      
ANN     SMYTH   BERLIN                      
CHARLES BERZINS BERLIN                      
NULL    NULL    NEW YORK                    
Unlike in Oracle where one can use both syntaxes without any restriction, SQL Server 2005 and 2008 by default cannot use it, one has to set compatibility level 80 (SQL Server 2000).

7 Equi join

To be sure that particular join is equi join one has to look at join condition. If it contains only equality operator in join conditions then it is equi join. Usually this is the most used join type. Even more - it is so often used that probably some people even cannot imagine the fact that something more than equality operator can be used.
There isn't special kind of syntax just for equi joins. If one looks at the scheme above it finds out following characteristics:
  • Natural join is always equi join because it has hidden join condition - equivalence of commonly named columns. Except of course when Natural join has degraded back to Cross join, because there isn't any common column;
  • Named columns joins are always equi joins because it has hidden join condition - equivalence of columns listed in USING clause;
  • Inner, outer and self joins can be or not be equi joins. It is dependant on operators in join condition;
  • It is not applicable to speak about cross join as equi or not as it haven't any join condition at all.

7.1 Examples

Most of until now used examples in this article were equi joins. Let's take this one:

Example 34 Selects first name, surname and city for every person. The same as Example 8.

SELECT prs_first_name, prs_surname, adr_city
FROM persons                                
INNER JOIN addresses ON prs_adr_id = adr_id;
                                            
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY    
--------------- --------------- ------------
PETER           BERZINS         BERLIN      
ANN             SMYTH           BERLIN      
CHARLES         BERZINS         BERLIN      

Example 35 Selects first name, surname and city for every person keeping all persons, even without address. The same as Example 19.

SELECT prs_first_name, prs_surname, adr_city     
FROM persons                                     
LEFT OUTER JOIN addresses ON prs_adr_id = adr_id;
                                                 
PRS_FIRST_NAME  PRS_SURNAME     ADR_CITY         
--------------- --------------- ---------------  
JANIS           BERZINS                          
PETER           BERZINS         BERLIN           
ANN             SMYTH           BERLIN           
CHARLES         BERZINS         BERLIN           

8 Non-Equi (Theta) join

Probably it is something new for you but join conditions can have operators other than equivalence. It is quite rarely used in practice, more just like a scientific possibility. On the other hand I'm quite sure one of the reasons why it is not used - is the fact that many people cannot imagine such possibility and overlook cases when it would be possible.
There isn't special syntax for these joins, except the fact that join condition must be explicitly used.

8.1 Facts to remember

  • Unless You my dear reader think You are real join virtuoso - do test non-equi joins on real data and with even more care than You test other SQL statements. They may produce surprising results if not used cautiously.

8.2 Examples

Example 36 Selects all pairs of first names where first one is longer than second one.

SELECT p1.prs_first_name, p2. prs_first_name             
FROM persons p1                                          
INNER JOIN persons p2                                    
ON length(p1.prs_first_name) > length(p2.prs_first_name);
                                                         
PRS_FIRST_NAME  PRS_FIRST_NAME                           
--------------- ---------------                          
CHARLES         JANIS                                    
CHARLES         PETER                                    
CHARLES         ANN                                      
JANIS           ANN                                      
PETER           ANN                                      
Probably not much real value in previous example. More real value can be in cases when one has somehow group people or whatever else. For example we have groups with dynamic upper and lower bounds stored in database. Let's imagine our persons are grouped according to their first name to different groups. We will create following table and add following data for that:
CREATE TABLE groups (                                
  grp_name VARCHAR(15) NOT NULL,                     
  grp_min_value VARCHAR(1) NOT NULL,                 
  grp_max_value VARCHAR(1) NOT NULL);                
INSERT INTO groups VALUES ('First group', 'A', 'G'); 
INSERT INTO groups VALUES ('Second group', 'H', 'N');
INSERT INTO groups VALUES ('Third group', 'O', 'Z'); 
COMMIT;                                              
Now we can split people to different groups according to their first name using operator BETWEEN. Of course on has to remember that only first symbol of each name should be used because otherwise for example name Gints won't be in any group because Gints is after lower bound of first group (G) but before than upper bound of second group (H).

Example 37 Selects each first name, surname and their group for each person.

SELECT prs_first_name, prs_surname, grp_name
FROM persons                                
INNER JOIN groups                           
ON substr(prs_first_name, 1, 1)             
  BETWEEN grp_min_value AND grp_max_value;  
                                            
PRS_FIRST_NAME  PRS_SURNAME     GRP_NAME    
--------------- --------------- ------------
ANN             SMYTH           First group 
CHARLES         BERZINS         First group 
JANIS           BERZINS         Second group
PETER           BERZINS         Third group 

9 Self join

With self joins it is quite simple. Self join is every other join type if both tables are the same. On the first spot probably it is not so obvious how to write such construction, but in this case I suggest following scenario:
  • Let's imagine we have two different tables with the same column names and the same data;
  • Let's create necessary SQL statement with join using two different tables and using aliases for their names. This time everything is quite clear?
  • Then just replace another table name with the same one. SQL statement is yet valid because everywhere aliases are used. And voila! That's it!
There isn't any special syntax for self joins, just used tables in them does matter.

9.1 Examples

Let's remember some of already used self joins throughout this article. All the following examples are self joins Example 7, Example 16, Example 18 and Example 25. The most interesting probably is this one:

Example 38 Example of table outer join to itself (self join) - two times table is joined to itself, as a result there are three instances of the same table in this SQL query. The same as Example 25.

SELECT father.prs_first_name "Father Name",                     
       mother.prs_first_name "Mother Name",                     
       child.prs_first_name "Child Name"                        
FROM persons child                                              
LEFT JOIN persons father ON child.prs_father_id = father.prs_id 
LEFT JOIN persons mother ON child.prs_mother_id = mother.prs_id;
                                                                
Father Name     Mother Name     Child Name                      
--------------- --------------- ---------------                 
                                JANIS                           
JANIS                           PETER                           
                                ANN                             
PETER           ANN             CHARLES                         
As we can observe there are 3 instances of the same table in one row: child, father and mother. And this usually is true - self joins are used in some kind recursive relationships. However one has to understand that self joins have limited count of recursions this can be 1, 2, ..., n but for unknown number of recursions one has to use recursive queries - START WITH .. CONNECT BY clause in Oracle and Common table expressions in SQL Server.

10 Natural join

Natural join is special kind of syntax. Unlike explicit inner and outer join syntax as described above natural join syntax has implicit join condition. It is both its strength and weakness - strength because one can write a bit less words, weakness - because it is quite dangerous and unprotected to structure changes of tables. What is this implicit join condition? Natural join combines two tables based on their common columns i.e. columns with the same name. Therefore join condition is hidden and dependant on table structures at runtime. This obviously creates potential future danger - as soon as table structure changes, result can become unpredictable yet syntactically correct.

10.1 Syntax

Syntax for Natural joins is as follows:
SELECT <column list>                         
FROM <left joined table>                     
NATURAL <join type> JOIN <right joined table>
Join type can be one of the following:
  • empty - this means Inner join;
  • INNER;
  • LEFT [OUTER] - keyword outer is optional;
  • RIGHT [OUTER] - keyword outer is optional;
  • FULL [OUTER] - keyword outer is optional.

10.2 Facts to remember

  • Natural join actually performs the same inner or one of the outer joins. It is just another syntactic convention.
  • Natural join always is equi-join; join condition always is equivalence between common columns.
  • If joined tables haven't common column then natural joins degrades back to Cartesian join. See Example 41.
  • Natural join syntax is dangerous and may lead to unpleasant surprises because of zero common columns between joined tables or too much common columns between joined tables. Therefore better never use it in real production program code. For more possible risks connected with natural join syntax see blog post Natural joins are evil.

10.3 Examples

Because natural joins need tables with the same column names example tables used earlier in this article are not usable. So we create new tables.
CREATE TABLE countries (                      
  cntr_id INTEGER NOT NULL PRIMARY KEY,       
  country VARCHAR(15) NOT NULL);              
CREATE TABLE cities (                         
   cty_id INTEGER NOT NULL PRIMARY KEY,       
   cntr_id INTEGER,                           
   city VARCHAR(15) NOT NULL);                
 CREATE TABLE places (                        
  plc_id INTEGER NOT NULL PRIMARY KEY,        
  cty_id INTEGER,                             
  text VARCHAR(15) NOT NULL);                 
INSERT INTO countries VALUES (1, 'LATVIA');   
INSERT INTO countries VALUES (2, 'ESTONIA');  
INSERT INTO cities VALUES (1, 1, 'RIGA');     
INSERT INTO cities VALUES (2, 1, 'LIEPAJA');  
INSERT INTO cities VALUES (3, 2, 'TALLINN');  
INSERT INTO places VALUES (1, 1, 'OLD RIGA'); 
INSERT INTO places VALUES (2, 2, 'KARA OSTA');
COMMIT;                                       

Example 39 Selects countries and cities using natural inner join syntax.

SELECT country, city      
FROM countries            
NATURAL INNER JOIN cities;
                          
COUNTRY         CITY      
--------------- ----------
LATVIA          RIGA      
LATVIA          LIEPAJA   
ESTONIA         TALLINN   

Example 40 Selects cities and places using natural left outer join syntax and keeping all cities.

SELECT city, text              
FROM cities                    
NATURAL LEFT OUTER JOIN places;
                               
CITY            TEXT           
--------------- ---------------
RIGA            OLD RIGA       
LIEPAJA         KARA OSTA      
TALLINN                        

Example 41 In case tables haven't common column name then natural join degrades back to Cartesian join.

SELECT country, text     
FROM countries           
NATURAL JOIN places;     
                         
COUNTRY         TEXT     
--------------- ---------
LATVIA          OLD RIGA 
LATVIA          KARA OSTA
ESTONIA         OLD RIGA 
ESTONIA         KARA OSTA
As soon as there are more than one common column in tables join condition is placed on all of them. Usually it is not what we really want. For example imagine we are adding both countries and cities a new column "notes". This column practically never will have the same values and it shouldn't be used in join condition. But natural join does that, and this time as all values are NULL, we get zero rows back.

Example 42 Selects countries and cities using natural inner join syntax. But another column is added for both tables' countries and cities. Compare with Example 39.

ALTER TABLE countries ADD notes VARCHAR(100);
ALTER TABLE cities ADD notes VARCHAR(100);   

SELECT country, city      
FROM countries            
NATURAL INNER JOIN cities;
                          
no rows selected          

11 Named columns join

Named columns join is some kind of enhanced version of natural join. One can explicitly name columns, which must be used in join condition. Therefore it isn't as dangerous as natural join but simply is just for limited use because not always column names to be joined are the same.

11.1 Syntax

SELECT <column list>                 
FROM <left joined table>             
<join type> JOIN <right joined table>
USING (<join column list>)           
Join type can be one of the following:
  • empty - this means Inner join;
  • INNER;
  • LEFT [OUTER] - keyword outer is optional;
  • RIGHT [OUTER] - keyword outer is optional;
  • FULL [OUTER] - keyword outer is optional.
In join column list one have to write comma delimited column list which are in both joined tables and are used to create equivalence condition. Named columns joins always are equi joins.

11.2 Facts to remember

  • This syntax type can be used only in case the joined columns have the same name in both involved tables.

11.3 Examples

We will use the same tables as defined for natural joins.

Example 43 Selects countries and cities using named columns join syntax. Compare with Example 42 and see how this time example is working again as expected.

SELECT country, city FROM countries
JOIN cities USING (cntr_id);       
                                   
COUNTRY         CITY               
--------------- ---------------    
LATVIA          RIGA               
LATVIA          LIEPAJA            
ESTONIA         TALLINN            

Example 44 Selects countries and cities using named columns join full outer join syntax and using column notes.

SELECT country, city FROM countries
FULL JOIN cities USING (notes);    
                                   
COUNTRY         CITY               
--------------- ---------------    
ESTONIA                            
LATVIA                             
                TALLINN            
                LIEPAJA            
                RIGA               

12 Example usage for various DBMSes

12.1 Oracle

All examples except Example 32, Example 33 (which were specifically created for SQL Server) were created and tested on Oracle 10g. They should work on Oracle versions since 9i, except probably on very early versions of 9i due to bugs in implementation of ANSI join syntax. But these versions you shouldn't use anyway.

12.2 Microsoft SQL Server

Examples were tested on SQL Server 2008. SQL Server doesn't support Natural join and Using clause syntax and therefore following examples are not valid for it Example 3, Example 4, and all examples starting with Example 39 till Example 44.
Following examples uses Oracle specific outer join operator and therefore isn't usable in SQL Server Example 30, Example 31.
In Example 36 function length should be replaced by function len.
In Example 37 function substr should be replaced by function substring.

12.3 MySQL

Examples were tested on MySQL 6.0. However there are some problems with them. In MySQL Cross Join is equivalent to Inner Join therefore Example 1 works as expected only because it lacks join condition.
Example 3, Example 4, Example 39 and Example 42 do not work because MySQL only partially supports Natural join syntax.
MySQL doesn't support Full Outer join therefore Example 22 and Example 44 do not work (Example 44 doesn't give an error because keyword FULL is considered as alias).
Following examples do not work because are specific to Oracle or SQL Server Example 30, Example 31, Example 32, Example 33.

13 References and more information

[1] Joins and join-like operators in wikipedia;
[2] SQL und relationale Algebra join examples in German;
[3] The power of the Cross Join - how to use cross joins in reports;
[4] Retrieval: Multiple Tables and Aggregation chapter from book Mastering Oracle SQL and SQL *Plus by Lex de Haan;
[5] What's in a Condition? - difference between old style Oracle outer join and SQL92 join syntax by Jonathan Gennick;
[6] Common errors seen when using OUTER-JOIN - common errors using old style Oracle outer join syntax (using (+) operator) by Kevin Meade;
[7] Natural joins are evil - article about possible great risks of natural join syntax;
[8] Documentation of joins in Oracle 11 release 1;
[9] Join fundamentals in SQL Server 2008;
[10] MySQL 6.0 Join syntax.

About the author

Gints Plivna gints.plivna@gmail.com is system analyst in Rix Technologies Ltd. and teaches official Oracle courses in Mebius Latvia. He has experience in working with Oracle since 1997 and his interests mostly have been connected with analyzing system requirements, design, development and SQL tuning.

Contacts:
e-mail - gints.plivna@gmail.com
website - http://www.gplivna.eu/

Licenses

This work is licensed under the Creative Commons Attribution-ShareAlike 2.5 License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/2.5/ or send a letter to Creative Commons, 543 Howard Street, 5th Floor, San Francisco, California, 94105, USA.

First edition: 2008-07-14
Added full outer join workaround and links to docs: 2008-07-28