SQL Joins - Visual Representation

What are joins?
Joins in SQL are fore combining records from two or more tables in a database. Join mean combining values from two tables using a common value, usual common identifier (ID) is used. Relational systems commonly call for joins, the key is efficient implementation of joins. You might want to follow up on indexing tables.

Equi-Join, Join and Inner Join

Inner shows only rows that share a common value in both tables. All of these three queries produce the same result. Equi-Join is the easiest way of using join. It doesn't require any join, you just select multiple tables and use where statement to define the common part. Join is same as inner join, basically join defaults to inner join.

Equi-Join

Source code viewer
SELECT * FROM table_a, table_b WHERE table_a.id = table_b.id
Programming Language: MySQL

Join

Source code viewer
SELECT * FROM table_a JOIN table_b ON table_a.id = table_b.id
Programming Language: MySQL

Inner Join

Source code viewer
SELECT * FROM table_a INNER JOIN table_b ON table_a.id = table_b.id
Programming Language: MySQL

Results

table_a.id	table_a.word	table_b.id	table_b.word
1	tutorial	1	database
2	sql	2	query

Left and Right Outer Joins in MySQL

Left outer join includes rows without common values from left table (table_a in example) and common valued rows (inner part). Right outer join works in same logic, just other way around.

Left Outer Join

Source code viewer
SELECT * FROM table_b LEFT OUTER JOIN table_a ON table_b.id = table_a.id
Programming Language: MySQL

Right Outer Join

Source code viewer
SELECT * FROM table_b RIGHT OUTER JOIN table_a ON table_b.id = table_a.id
Programming Language: MySQL

Results - Left Outer Join

table_a.id	table_a.word	table_b.id	table_b.word
1	tutorial	1	databose
2	sql	2	query
3	example	(NULL)	(NULL)
4	test	(NULL)	(NULL)

Results - Right Outer Join

table_a.id	table_a.word	table_b.id	table_b.word
1	tutorial	1	databose
2	sql	2	query
(NULL)	(NULL)	5	join
(NULL)	(NULL)	6	image

Left and Right Outer Joins without Inner

Does the same as Left and right outer joins but excludes the inner part using where statement.

Left Outer Join without Inner

Source code viewer
SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id WHERE table_b.id IS null
Programming Language: MySQL

Right Outer Join without Inner

Source code viewer
SELECT * FROM table_a RIGHT OUTER JOIN table_b ON table_a.id = table_b.id WHERE table_a.id IS null
Programming Language: MySQL

Results - Left Outer Join without Inner

table_a.id	table_a.word	table_b.id	table_b.word
3	example	(NULL)	(NULL)
4	test	(NULL)	(NULL)

Results - Right Outer Join without Inner

table_a.id	table_a.word	table_b.id	table_b.word
(NULL)	(NULL)	5	join
(NULL)	(NULL)	6	image

Full Outer Join

MySQL does not have full outer joins, but you can emulate them, by doing union on outer joins that exclude the inner part.

Full Outer Join

Source code viewer
SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id WHERE table_b.id IS null
UNION
SELECT * FROM table_a RIGHT OUTER JOIN table_b ON table_a.id = table_b.id WHERE table_a.id IS null
Programming Language: MySQL

Results

table_a.id	table_a.word	table_b.id	table_b.word
3	example	(NULL)	(NULL)
4	test	(NULL)	(NULL)
(NULL)	(NULL)	5	join
(NULL)	(NULL)	6	image

Full Outer Join with Inner

MySQL does not have full outer joins, but you can emulate them. This select all values from both tables by having some values joined with the common identifier.

Full Outer Join without Inner

Source code viewer
SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id
UNION
SELECT * FROM table_a RIGHT OUTER JOIN table_b ON table_a.id = table_b.id
Programming Language: MySQL

Results

table_a.id	table_a.word	table_b.id	table_b.word
1	tutorial	1	databose
2	sql	2	query
3	example	(NULL)	(NULL)
4	test	(NULL)	(NULL)
(NULL)	(NULL)	5	join
(NULL)	(NULL)	6	image