Types of joins in SQL (cheatsheet for beginners)

Submitted by Serguei_Tarassov on

Better to see once than to hear a hundred times. This small article explains what are the different kinds of joins in SQL.

At the first time you should take into account that SQL is a declarative 4th generation language. So the best way to use it (especially for beginners) is to write the query and let SQL optimizer do its job. That means thinking in set-oriented terms and avoiding sub-queries.

Let's take two example tables: Companies and Contacts.

CREATE TABLE companies (
  company_id INT NOT NULL,
  company_name VARCHAR(64) NOT NULL,
  phone VARCHAR(16) NULL,
  CONSTRAINT pk_companies PRIMARY KEY (company_id)
);
 
CREATE TABLE contacts (
  contact_id INT NOT NULL,
  contact_name VARCHAR(64),
  phone VARCHAR(16) NULL,
  company_id INT NULL,
  CONSTRAINT pk_contacts PRIMARY KEY (contact_id),
  CONSTRAINT fk_contact_company
    FOREIGN KEY (company_id)
    REFERENCES companies(company_id)
);

Fill these tables by following data:

INSERT INTO companies VALUES (1, 'AT&T', null);
INSERT INTO companies VALUES (2, 'Microsoft', '322-223');
INSERT INTO contacts VALUES (1, 'Bjarne Stroustrup', null, 1);
INSERT INTO contacts VALUES (2, 'Niklaus Wirth', '322-223', null);
INSERT INTO contacts VALUES (3, 'Bill Gates', '322-223', 2);

Here are the tables with data:

Companies

company_id company_name phone
1 AT&T NULL
2 Microsoft 322-223

Contacts

contact_id contact_name phone company_id
1 Bjarne Stroustrup NULL 1
2 Niklaus Wirth 322-223 NULL
3 Bill Gates 322-223 2

Now it's time to write queries.

INNER JOIN

SELECT contact_name, company_name
FROM contacts 
     INNER JOIN companies
     ON contacts.company_id = companies.company_id
ORDER BY contact_name

contact_name company_name
Bjarne Stroustrup AT&T
Bill Gates Microsoft

LEFT OUTER JOIN

SELECT contact_name, company_name
FROM contacts 
     LEFT OUTER JOIN companies
     ON contacts.company_id = companies.company_id
ORDER BY contact_name

contact_name company_name
Bjarne Stroustrup AT&T
Niklaus Wirth NULL
Bill Gates Microsoft

RIGHT OUTER JOIN

In this example tables are joint by a non-key attribute. In relational model you don't have to think of the physical link existence.

SELECT contact_name, company_name
FROM contacts 
     RIGHT OUTER JOIN companies
     ON contacts.phone = companies.phone
ORDER BY contact_name

contact_name company_name
NULL AT&T
Niklaus Wirth Microsoft
Bill Gates Microsoft

Let's write the "left outer join" too because it's required for the next example.

SELECT contact_name, company_name
FROM contacts 
     LEFT OUTER JOIN companies
     ON contacts.phone = companies.phone
ORDER BY contact_name

contact_name company_name
Bjarne Stroustrup NULL
Niklaus Wirth Microsoft
Bill Gates Microsoft

FULL JOIN

SELECT contact_name, company_name
FROM contacts 
     FULL OUTER JOIN companies
     ON contacts.phone = companies.phone
ORDER BY contact_name

As you can see, FULL JOIN is the union of results returned by LEFT and RIGHT outer joins.

contact_name company_name
NULL AT&T
Bjarne Stroustrup NULL
Niklaus Wirth Microsoft
Bill Gates Microsoft

CROSS JOIN is the Cartesian product

SELECT contact_name, company_name
FROM contacts 
     CROSS JOIN companies
ORDER BY contact_name

contact_name company_name
Bjarne Stroustrup AT&T
Bjarne Stroustrup Microsoft
Niklaus Wirth AT&T
Niklaus Wirth Microsoft
Bill Gates AT&T
Bill Gates Microsoft

Have a nice SQL!

It's a translation of my article published in April 2006 in "PC World (Russian edition)", N°12, 2007