1They are functionally equivalent, but INNER JOIN can be
2a bit clearer to read, especially if the query has
3other join types (i.e. LEFT or RIGHT or CROSS)
4included in it.
1-- Rows with ID existing in both a and b
2-- JOIN is equivalent to INNER JOIN
3SELECT a.ID, a.NAME, b.VALUE1 FROM table1 a
4 JOIN table2 b ON a.ID = b.ID
5WHERE a.ID >= 1000;
6-- ⇓ Test it ⇓ (Fiddle source link)
1INNER JOIN:
2is used when retrieving data from multiple
3tables and will return only matching data.
4
5LEFT OUTER JOIN:
6is used when retrieving data from
7multiple tables and will return
8left table and any matching right table records.
9
10RIGHT OUTER JOIN:
11is used when retrieving data from
12multiple tables and will return right
13table and any matching left table records
14
15FULL OUTER JOIN:
16is used when retrieving data from
17multiple tables and will return both
18table records, matching and non-matching.
1INNER JOIN:
2is used when retrieving data from multiple
3tables and will return only matching data.
4
5
6FULL OUTER JOIN:
7is used when retrieving data from
8multiple tables and will return both
9table records, matching and non-matching.