この記事は公開されてから半年以上経過しています。情報が古い可能性がありますので、ご注意ください。

SQL JOINS

An instruction to a database to combine data from more than one table.

Mostly used SQL Join:

INNER JOIN : Returns all rows When there IS at least one match in BOTH tables.
LEFT JOIN : Return all rows from the left table, and the matched rows from the right table.

Other commonly used SQL Join:

RIGHT JOIN : Return all rows from the right table, and the matched rows from the left table.
FULL JOIN : Return all rows When there's a matched in ONE of the tables.

**USERS TABLE**
id	first_name	last_name	age
1	ryan	bang	24
2	jow	nathan	34
3	wilson	chandler	31
4	jerald	green	22

**PRODUCTS TABLE**
id	user_id	product	category
1	4	casserole	kitchen
2	2	grass cutter	garage
3	1	wiper	general
4	1	rice cooker	electronic

♦ LEFT JOIN or LEFT OUTER JOIN

A left outer join, or left join, results in a set where all of the rows from the first, or left hand side, table are preserved.
The rows from the Second, or right hand side table only show up if they have a matched with the rows from the first table.
Where there Are values from the left table but not from the right, the table Will read null , Which Means That the value HAS not Been set.

example :

SELECT user.id, user.first_name, user.last_name, product.name, product.category
FROM users
LEFT JOIN products
ON user_id = id;

Result :

id	first_name	last_name	age	name	category
1	ryan	bang	24	wiper	general
1	ryan	bang	24	rice cooker	electronic
2	jow	nathan	34	grass cutter	garage
3	wilson	chandler	31	null	null
4	jerald	green	22	casserole	kitchen

Note :
On the Other hand, LEFT JOIN has some downfall .
If there Are much Users, you Have to Check all users even if you only need the user HAVING some Products.

♦ INNER JOIN

An Inner join Produces a result set That IS Limited to the rows where there's a matched in both tables for what we're looking for.

example :

SELECT user.id, user.first_name, user.last_name, product.name, product.category
FROM users
INNER JOIN products
ON user_id = id;

Result :

id	first_name	last_name	age	name	category
1	ryan	bang	24	wiper	general
1	ryan	bang	24	rice cooker	electronic
2	jow	nathan	34	grass cutter	garage
4	jerald	green	22	casserole	kitchen

Other query example :

select * from users INNER JOIN products on
id = user_id;

This can also be written as:

select * from users, products where id = user_id;

Reference: SQL Joins

Happy Programming Everyone !!!

AWS・クラウドのMSP スカイアーチネットワークス株式会社

AWS・クラウド導入における構築から監視・運用保守までをマネージドサービスで提供する、スカイアーチネットワークスの公式サイト。AWS・クラウドを始め、豊富なサーバー管理の経験と安心のセキュリティ対策、専門エンジニアの確かな知識で、お客様のビジネスの成長をサポートします。AWS・クラウド導入における構築から監視・運用保守までをマネージドサービスで提供する、スカイアーチネットワークスの公式サイト。AWS・クラウドを始め、豊富なサーバー管理の経験と安心のセキュリティ対策、専門エンジニアの確かな知識で、お客様のビジネスの成長をサポートします。

投稿者プロフィール

スカイブロガー

Understanding SQL Joins

SQL JOINS