JOINS

Joins are used to
connect two or more tables based on foreign key.

SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 ON (table1.column_name = table2.column_name)] |
[LEFT | RIGHT | FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)] |
[CROSS JOIN table2];

Here is Oracle specific syntax

SELECT table1.column, table2.column
FROM table1, table2
[WHERE (table1.column_name = table2.column_name)] |
[WHERE (table1.column_name(+)= table2.column_name)] |
[WHERE (table1.column_name)= table2.column_name) (+)] ;

NATURAL JOIN

The pure natural join identifies the columns with common names in table1 and
table2 and implicitly joins the tables using all these columns. Like in this example both tables have COUNTRY_ID field

SELECT * FROM locations NATURAL JOIN countries;

It is also possible to use USING keyword to specify which is the field on which tables should join. We do not use word NATURAL then.

SELECT * FROM locations JOIN countries USING country_id;

Natural join with ON clause allows joining on specific columns regardless of their name. This one also does not use the word NATURAL.

SELECT * FROM departments d
JOIN employees e ON (e.employee_id=d.department_id);

INNER JOIN
Inner join connects two tables on rows that match.

SELECT * FROM
table1 t1 INNER JOIN table2 t2
ON (t1.table1_id = t2.table2_foreign_key_id);

There is also Oracle specific way to write INNER JOIN

SELECT * FROM
table1 t1, table2 t2
WHERE (t1.table1_id = t2.table2_foreign_key_id);

There is a third way to do INNER JOIN but it is rarely used, because the fields from the two tables need to have exactly the same name (in this example same_name_id)

SELECT * FROM
table1 t1 INNER JOIN table2 t2
USING (same_name_id);

LEFT and RIGHT OUTER JOIN

These joins copy all the rows from one table and add matching rows from the other table or null if there are none.

SELECT t1.column, t2.column FROM
table1 t1 LEFT OUTER JOIN table2 t2
ON (t1.some_id = t2.some_foreign_key_id);

and in Oracle syntax (+) stands next to a table we want to outer join. In this example it is LEFT JOIN.

SELECT t1.column, t2.column FROM
table1 t1, table2 t2
WHERE t1.some_id(+) = t2.some_foreign_key_id;

FULL OUTER JOIN

This join shows all the rows from both the tables, regardless of the fact whether left or right table is missing a value (being NULL)
SELECT t1.column, t2.column FROM
table1 t1 FULL OUTER JOIN table2 t2
ON (t1.some_id = t2.some_foreign_key_id);

CARTESIAN JOIN

There is also a thing called Cartesian Join (or cross join) but it is so rarely used in practice that I will just link here to an explanation of it LINK.

JOIN MULTIPLE TABLES

It is possible to join more than 2 tables

SELECT t1.some_column, t2.other_column, t3.third_column
FROM table1 t1 INNER JOIN table2 t2
ON (t1.id = t2.fk_id)
INNER JOIN table3 t3
ON (t2.id = t3.fk_id)

SELF JOIN

It is possible to join table to itself.

SELECT * FROM table1 a
INNER JOIN table1 b
WHERE (a.id = b.different_id);

NONEQUIJOINS

Joining tables on non equal values of columns.

SELECT table1.column, table2.column
FROM table1
[JOIN table2 ON (table1.column_name table2.column_name)]|
[JOIN table2 ON (table1.column_name = table2.column_name)]|
[JOIN table2 ON (table1.column BETWEEN table2.col1 AND table2.col2)]|

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: