1/*Two tables: CUSTOMERS table and ORDERS table.
2ORDERS table contains STATUS attribute.*/
3SELECT
4 customers.customerNumber,
5 customerName,
6 orderNumber,
7 status
8FROM
9 customers
10LEFT JOIN orders ON
11 orders.customerNumber = customers.customerNumber;
1SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
2FROM Orders
3INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
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.
19
20
21
22INNER JOIN :
23SELECT select_list From TableA A
24Inner Join TableB B
25On A.Key = B.Key
26
27
28LEFT OUTER JOIN :
29SELECT select_list From TableA A
30Left Join TableB B
31On A.Key = B.Key
32
33(where b.key is null)//For delete matching data
34
35
36
37RIGTH OUTER JOIN :
38SELECT select_list From TableA A
39Right Join TableB B
40On A.Key = B.Key
41
42
43FULL JOIN :
44SELECT select_list From TableA A
45FULL OUTER Join TableB B
46On A.Key = B.Key
47
48
1-- Rows with ID existing in both a, b and c
2-- JOIN is equivalent to INNER JOIN
3SELECT a.ID, a.NAME, b.VALUE1, c.VALUE1 FROM table1 a
4 JOIN table2 b ON a.ID = b.ID
5 JOIN table3 c ON a.ID = c.ID
6WHERE a.ID >= 1000;
7-- ⇓ Test it ⇓ (Fiddle source link)
1Suppose we are having three table named as
2Student_details
3Attendance_details
4Batch_details
5And we have to apply join these three tables for fetching records
6
7Example query:
8select column_names
9from Student_detail as s join Attendance_details as a on
10s.s_id = a.s_id join Batch_details as b on
11s.s_id = b.s_id;
12
13Here in the above example we implemented simple join but you change it with own join requirements.
1SELECT Coloumn_Name(s) FROM Table_1, Table_2 WHERE Table_1.Primary_key = Table_2.Foreign_key;