xxxxxxxxxx
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer;
xxxxxxxxxx
-- Rows with ID existing in both a, b and c
-- JOIN is equivalent to INNER JOIN
SELECT a.ID, a.NAME, b.VALUE1, c.VALUE1 FROM table1 a
JOIN table2 b ON a.ID = b.ID
JOIN table3 c ON a.ID = c.ID
WHERE a.ID >= 1000;
-- ⇓ Test it ⇓ (Fiddle source link)
xxxxxxxxxx
INNER JOIN:
is used when retrieving data from multiple
tables and will return only matching data.
LEFT OUTER JOIN:
is used when retrieving data from
multiple tables and will return
left table and any matching right table records.
RIGHT OUTER JOIN:
is used when retrieving data from
multiple tables and will return right
table and any matching left table records
FULL OUTER JOIN:
is used when retrieving data from
multiple tables and will return both
table records, matching and non-matching.
INNER JOIN :
SELECT select_list From TableA A
Inner Join TableB B
On A.Key = B.Key
LEFT OUTER JOIN :
SELECT select_list From TableA A
Left Join TableB B
On A.Key = B.Key
(where b.key is null)//For delete matching data
RIGTH OUTER JOIN :
SELECT select_list From TableA A
Right Join TableB B
On A.Key = B.Key
FULL JOIN :
SELECT select_list From TableA A
FULL OUTER Join TableB B
On A.Key = B.Key
xxxxxxxxxx
#INNER JOIN: Intersection between 2 tables
SELECT *
FROM A
INNER JOIN B ON A.key = B.key
#LEFT JOIN: left table with the intersection joined to the right table
SELECT *
FROM A
LEFT JOIN B ON A.key = B.key
#LEFT JOIN(without intesection): left table without the intersection joined
# to the right table
SELECT *
FROM A
LEFT JOIN B ON A.key = B.key
WHERE B.key IS NULL
#RIGHT JOIN: right table with the intersection joined to the left table
SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key
#RIGHT JOIN(without intesection): right table without the intersection joined
# to the left table
SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key
WHERE A.key IS NULL # there seems to be a mistake on the site (B changed to A)
#FULL JOIN: union of 2 table
SELECT *
FROM A
FULL JOIN B ON A.key = B.key
#FULL JOIN(without intesection): union of two table without the intersection
SELECT *
FROM A
FULL JOIN B ON A.key = B.key
WHERE A.key IS NULL
OR B.key IS NULL
xxxxxxxxxx
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
xxxxxxxxxx
SELECT table1.column1,table1.column2,table2.column1, .
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
table1: First table.
table2: Second table
matching_column: Column common to both the tables.
xxxxxxxxxx
SELECT orders.order_Number, customers.First_Name, customers.Last_Name, customers.Address
FROM orders
INNER JOIN Customers ON orders.customers_id=customers.id;
xxxxxxxxxx
LEFT JOIN
All rows from the left table will be returned, even if there's no matching row in the right table.
RIGHT JOIN
All rows from the right table will be returned, even if there's no matching row in the left table.
INNER JOIN
Only returns rows where there's a matching row in both tables.
xxxxxxxxxx
-- TYPE can be INNER, FULL, LEFT, RIGHT, CROSS (Addittive joins)
SELECT left.colx, right.coly, another.colz
FROM left_table as left
<TYPE> JOIN right_table as right -- First join
ON left.common_col = right.common_col
<TYPE> JOIN another_table as another -- Second chaining join
ON right.colz = another.colz AND right.coly = another.coly -- multiple column matching condition
WHERE left.colx = 'val'
-- Semi Join
SELECT left_table.*
FROM left_table
WHERE left_table.some_col IN (
SELECT another_col
FROM right_table
);
-- Anti Join (Inverse of Semi join, add a NOT keyword)
SELECT left_table.*
FROM left_table
WHERE left_table.some_col NOT IN (
SELECT another_col
FROM right_table
);