SQL JOIN

Joins and Keys

Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join.

Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.

In the "Authors" table below, the "Author" column is the primary key, meaning that no two rows can have the same Author field. The Author field distinguishes two authors.

When you look at the example tables below, notice that:

The "Author" column is the primary key of the "Authors" table
The "Title" column is the primary key of the "Books" table
The "Author" column in the "Books" table is used to refer to the author in the "Authors" table

The "Books" table:

Title	Author	Publisher	Year
الدورة الدموية	إبن النفيس	دار العلم	1650
Java 2	L. Johnston	Fast Press	2002
Linux and Unix	J. Sam	Fast Press	2004
Operating Systems	M. Stone	Coriolis	2005
Web Programming	K. Yariv	East Edition	2005
XML Language	M. Salim	Knowledge Press	2000

The "Authors" table:

Author	Email	Country
إبن النفيس		الشام
H. Saleh	saleh@ahram.eg	Egypt
J. Sam	sam@soft.com	Japan
J. Schmidt	schmidt@web.de	Germany
J. Xue	xue@science.cn	China
K. Yariv	yariv@east.ru	Russia
L. Johnston	johnston@science.com	India
M. Salim	salim@salam.com	Pakistan

Referring to Two Tables

We can select data from two tables by referring to two tables, like this:

Example

To display the title of the book and the country of its author, we use the following SQL:

SELECT Books.Title, Authors.Country FROM Books, Authors WHERE Books.Author = Authors.Author;

The result is:

Title	Country
الدورة الدموية	الشام
Java 2	India
Linux and Unix	Japan
Web Programming	Russia
XML Language	Pakistan

Example

Which book is written by an Indian author?

SELECT Books.Title, Books.Author, Authors.Country FROM Books, Authors WHERE Books.Author = Authors.Author AND Authors.Country = 'India';

The result is:

Title	Author	Country
Java 2	L. Johnston	India

Using Joins

OR we can select data from two tables with the JOIN keyword, like this:

Example INNER JOIN

Syntax

SELECT columnName1, columnName2, columnName3 FROM tableName1 INNER JOIN tableName2 ON tableName1.keyColumnName = tableName2.foreignKeyColumnName;

To display the title of the book and the country of its author, we use the following SQL:

SELECT Books.Title, Authors.Country FROM Books INNER JOIN Authors ON Books.Author = Authors.Author;

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in a table that do not have matches in the other table, those rows will not be listed.

The result is:

Title	Country
الدورة الدموية	الشام
Java 2	India
Linux and Unix	Japan
Web Programming	Russia
XML Language	Pakistan

Example LEFT JOIN

Syntax

SELECT columnName1, columnName2, columnName3 FROM tableName1 LEFT JOIN tableName2 ON tableName1.keyColumnName = tableName2.foreignKeyColumnName;

List all employees, and their orders - if any.

SELECT Books.Title, Authors.Country FROM Books LEFT JOIN Authors ON Books.Author = Authors.Author;

The result is:

Title	Country
الدورة الدموية	الشام
Java 2	India
Linux and Unix	Japan
Operating Systems	null
Web Programming	Russia
XML Language	Pakistan

The LEFT JOIN returns all the rows from the first table (Books), even if there are no matches in the second table (Authors). i.e. If there are rows in "Books" table that do not have matches in "Authors" table, those rows also will be listed and the fields of the second table will be replaced by "null" or empty string (see the 4^th row in the last table).

Example RIGHT JOIN

Syntax

SELECT columnName1, columnName2, columnName3 FROM tableName1 RIGHT JOIN tableName2 ON tableName1.keyColumnName = tableName2.foreignKeyColumnName;

List all orders, and who has ordered - if any.

SELECT Books.Title, Authors.Country FROM Books RIGHT JOIN Authors ON Books.Author = Authors.Author;

The result is:

Title	Country
الدورة الدموية	الشام
null	Egypt
Linux and Unix	Japan
null	Germany
null	China
Web Programming	Russia
Java 2	India
XML Language	Pakistan

The RIGHT JOIN returns all the rows from the second table (Authors), even if there are no matches in the first table (Books). i.e. If there are rows in "Authors" table that do not have matches in "Books" table, those rows also will be listed and the fields of the first table will be replaced by "null" or empty string (see the 2^nd, 4^th, and 5^th rows in the last table).

Example

Which book is written by an Indian author?

SELECT Books.Title, Books.Author, Authors.Country FROM Books INNER JOIN Authors ON Books.Author = Authors.Author WHERE Authors.Country = 'India';

The result is:

Title	Author	Country
Java 2	L. Johnston	India

Test your SQL Skills

To test your SQL skills click here.