SQL Joins - Quick examples

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

Tables in a database are often related to each other with keys.

A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

INNER JOIN:	Return rows when there is at least one match in both tables
OUTER LEFT JOIN:	Return all rows from the left table, even if there are no matches in the right table
OUTER RIGHT JOIN:	Return all rows from the right table, even if there are no matches in the left table
FULL JOIN:	Return rows when there is a match in one of the tables

Tables used for example:

CREATE TABLE cities (

    name character varying(20),

    location character varying(10)

);

CREATE TABLE weather (

    city character varying(20),

    temp_lo integer,

    temp_hi integer

);

Inner join

The INNER JOIN keyword returns rows when there is at least one match in both tables. If there are rows in "Cities" that do not have matches in "Weather", those rows will NOT be listed and vice versa.

IN the below example the "Delhi" and "Chennai" are the ones that are in both Cities and weather

SELECT * FROM cities INNER JOIN weather ON (cities.name=weather.city);

Left outer join

This query is called a left outer join because the table mentioned on the left of the join operator will have each of its rows in the output at least once, whereas the table on the right will only have those rows output that match some row of the left table. When outputting a left-table row for which there is no right-table match, empty (null) values are substituted for the right-table columns.

In the below example, "Calcutta "/"Delhi"/"Chennai" from cities are the selected rows, eventhough weather does not have "Calcutta"

SELECT * FROM cities LEFT OUTER JOIN weather ON (cities.name=weather.city);

Right outer join

This query is called a right outer join because the table mentioned on the right of the join operator will have each of its rows in the output at least once, whereas the table on the left will only have those rows output that match some row of the right table. When outputting a right-table row for which there is no left-table match, empty (null) values are substituted for the left-table columns.

In the Below Example, "Delhi"/"Chennai"/"Bangalore" from weather are the selected rows eventhough cites does not contain "Bangalore".

SELECT * FROM cities RIGHT OUTER JOIN weather ON (cities.name=weather.city);

Full Join

The FULL JOIN keyword return rows when there is a match in one of the tables.

IN the Below Example "Delhi"/"Chennai"/"Calcutta"/"Bangalore" all rows from cities and weather are listed.

SELECT * FROM cities FULL JOIN weather ON (cities.name=weather.city);

Self join

We can also join a table against itself. This is called a self join. 

As an example, suppose we wish to find all the weather records that are in the temperature range of other weather records. So we need to compare the temp_lo and temp_hi columns of each weather row to the temp_lo and temp_hi columns of all other weather rows. We can do this with the following query:
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, 

W2.city, W2.temp_lo AS low, W2.temp_hi AS high 

FROM weather W1, weather W2WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;

References

http://www.w3schools.com/sql/sql_join.asp