SQL: 06 - JOIN and UNION

JOIN

JOIN tabulates columns from two or more tables WITH SAME NAMES

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables(default)

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

• FULL JOIN: Return all rows when there is a match in ONE of the tables

Syntax

 

Example

SELECT medialist.MediaType, medialist.MediaColor, medialist.MediaBrand
FROM medialist
INNER JOIN MediaPriceListTable
ON medialist.MediaType=’brush’

 

Drupal DB Examples

select node.nid, node.type, node_field_data.title
from node
LEFT OUTER JOIN node_field_data
ON node.nid = node_field_data.nid

 

nid    type    title
1    article    Outlaw Testing, LLC
2    article    Map & Contact
3    article    About
4    article    Background Styling
5    article    FakePage

 

select node.nid, node.type, node_field_data.title, node_field_data.created
from node
LEFT OUTER JOIN node_field_data
ON node.nid = node_field_data.nid

 

nid    type    title    created
1    article    Outlaw Testing, LLC    1528428232
2    article    Map & Contact    1528428881
3    article    About    1528431620
4    article    Background Styling    1528432286
5    article    FakePage    1529885694

 

UNION

UNION tabulates columns from two or more tables WITH DIFFERENT NAMES

Syntax

 

Example