Understanding SQL Joins

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.
id first_name last_name age
1 ryan bang 24
2 jow nathan 34
3 wilson chandler 31
4 jerald green 22
USERS TABLE
id user_id product category
1 4 casserole kitchen
2 2 grass cutter garage
3 1 wiper general
4 1 rice cooker electronic
PRODUCTS TABLE

♦ 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 !!! smiley_skyarch

コメントを残す

メールアドレスが公開されることはありません。

Time limit is exhausted. Please reload CAPTCHA.