You are here

22 July 2013
Full guide for MySQL joins. Visual data and query examples should make the subject perfectly clear or you can bookmark it as a good reference. So here it is, the visual representation of sql (mysql) joins.
What are joins in MySQL?
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.
MySQL Equi-Join, Join and Inner Join
Equi-Join
Source code viewer
  1. SELECT * FROM table_a, table_b WHERE table_a.id = table_b.id
Programming Language: MySQL
Join
Source code viewer
  1. SELECT * FROM table_a JOIN table_b ON table_a.id = table_b.id
Programming Language: MySQL
Inner Join
Source code viewer
  1. SELECT * FROM table_a INNER JOIN table_b ON table_a.id = table_b.id
Programming Language: MySQL
Results
table_a.idtable_a.wordtable_b.idtable_b.word
1tutorial1database
2sql2query

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   Right Outer Join
Left Outer Join
Source code viewer
  1. SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id
Programming Language: MySQL
Right Outer Join
Source code viewer
  1. SELECT * FROM table_a RIGHT OUTER JOIN table_b ON table_a.id = table_b.id
Programming Language: MySQL
Results - Left Outer Join
table_a.idtable_a.wordtable_b.idtable_b.word
1tutorial1databose
2sql2query
3example(NULL)(NULL)
4test(NULL)(NULL)
Results - Right Outer Join
table_a.idtable_a.wordtable_b.idtable_b.word
1tutorial1databose
2sql2query
(NULL)(NULL)5join
(NULL)(NULL)6image

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 without Inner   Right Outer without Inner
Left Outer Join without Inner
Source code viewer
  1. 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
  1. 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.idtable_a.wordtable_b.idtable_b.word
3example(NULL)(NULL)
4test(NULL)(NULL)
Results - Right Outer Join without Inner
table_a.idtable_a.wordtable_b.idtable_b.word
(NULL)(NULL)5join
(NULL)(NULL)6image

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
Full Outer Join
Source code viewer
  1. SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id WHERE table_b.id IS null
  2. 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.idtable_a.wordtable_b.idtable_b.word
3example(NULL)(NULL)
4test(NULL)(NULL)
(NULL)(NULL)5join
(NULL)(NULL)6image

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 without Inner
Full Outer Join without Inner
Source code viewer
  1. SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id
  2. SELECT * FROM table_a RIGHT OUTER JOIN table_b ON table_a.id = table_b.id
Programming Language: MySQL
Results
table_a.idtable_a.wordtable_b.idtable_b.word
1tutorial1databose
2sql2query
3example(NULL)(NULL)
4test(NULL)(NULL)
(NULL)(NULL)5join
(NULL)(NULL)6image