Multi-tabular databases

JOIN

JOIN

  • (INNER) JOIN

  • LEFT (OUTER) JOIN

  • RIGHT (OUTER) JOIN

  • FULL (OUTER) JOIN

JOIN

join

Examples

Tables

user_id

name

fullname

1

Ник

Никольский

2

Майк

Майкович

invoice_id

user_id

product_id

1

1

7

2

2

12

INNER JOIN

SELECT invoice.invoice_id, users.name
FROM Orders
INNER JOIN users ON invoice.user_id = users.user_id;

LEFT JOIN

SELECT users.name, invoice.invoice_id
FROM users
LEFT JOIN invoice ON users.user_id = invoice.user_id
ORDER BY users.name;

RIGHT JOIN

SELECT invoice.invoice_id, users.name, users.fullname
FROM invoice
RIGHT JOIN users ON invoice.user_id = users.user_id
ORDER BY invoice.invoice_id;

FULL JOIN

SELECT users.name, invoice.invoice_id
FROM users
FULL OUTER JOIN invoice ON users.user_id = invoice.user_id
ORDER BY users.name;