SQL Joins

Follow @harshit_parshii

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

FULL OUTER JOIN

A JOIN is made matching a column on a table to a column on the other table. After a FULL OUTER JOIN, for a given value (red), for a given row with this value on one table ([ red | 9999 ]), one row is created for each row that matches on the other table ([ red | OOOOOO ] and [ red | LLLLLL ]). If a value exists in only one table, then a row is created and is completed with NULL columns.

FROM table_1 FULL OUTER JOIN table_2 ON table_1.common_value = table_2.common_value

common_value	specific_value_1	specific_value_2
red	9999	OOOOOO
red	9999	LLLLLL
grey	6666	NULL
white	0000	NULL
purple	7777	NULL
purple	2222	NULL
black	8888	FFFFFF
green	NULL	HHHHHH
yellow	NULL	PPPPPP
blue	NULL	RRRRRR

RIGHT OUTER JOIN 

The RIGHT OUTER JOIN is like the FULL OUTER JOIN but it doesn't create row for values that don't exist on the left table.

FROM table_1 RIGHT OUTER JOIN table_2 ON table_1.common_value = table_2.common_value

common_value	specific_value_1	specific_value_2
red	9999	OOOOOO
red	9999	LLLLLL
black	8888	FFFFFF
green	NULL	HHHHHH
yellow	NULL	PPPPPP
blue	NULL	RRRRRR

LEFT OUTER JOIN 

The LEFT OUTER JOIN is like the FULL OUTER JOIN but it doesn't create row for values that don't exist on the right table.

FROM table_1 LEFT OUTER JOIN table_2 ON table_1.common_value = table_2.common_value

common_value specific_value_1 specific_value_2 red 9999 OOOOOO red 9999 LLLLLL grey 6666 NULL white 0000 NULL purple 7777 NULL purple 2222 NULL black 8888 FFFFFF

INNER JOIN 

The INNER JOIN is like the FULL OUTER JOIN but it creates row only for values that exist on both the left table and the right table.

FROM table_1 INNER JOIN table_2 ON table_1.common_value = table_2.common_value

common_value specific_value_1 specific_value_2 red 9999 OOOOOO red 9999 LLLLLL black 8888 FFFFFF

I hope things are pretty clear here.