SQL Joins

2021年1月25日
技術情報

Today I would like to talk about MySQL joins along with some practical example queries.

Inner Join

２テーブルの中で同じレコードだけ出る。
(return only matching records from both tables)

Left Join

left テーブルは全レコードが出る、right テーブルのは同じレコードだけ
(return all records from the left table, and only the matched records from the right table)

Right Join

right テーブルは全レコードが出る、leftテーブルのは同じレコードだけ
(return all records from the right table, and only the matched records from the left table)

Full Outer Join

同じレコードが有る時全部レコードが出る。
(return all records from both tables if there is match record between them)

Inner Join Query Example

SELECT column(s)
FROM tb1
INNER JOIN tb2
ON tb1.column = tb2.column;

Left Join Query Example

SELECT column(s)
FROM tb1
LEFT JOIN tb2
ON tb1.column = tb2.column;

Right Join Query Example

SELECT column(s)
FROM tb1
RIGHT JOIN tb2
ON tb1.column = tb2.column;

Full Outer Join Query Example

mysql doesn’t support full join syntax directly so we can use this as an alternative.
(full join = left + right join)

SELECT column(s)
FROM tb1
LEFT JOIN tb2 ON tb1.column = tb2.column;
LEFT JOIN tb3 ON tb1.column = tb3.column;
UNION
SELECT column(s)
FROM tb1
SELECT column(s)
FROM tb1
RIGHT JOIN tb2 ON tb1.column = tb2.column;
RIGHT JOIN tb3 ON tb1.column = tb3.column;