JOIN
- Joins are used to extract data from one or more tables.
- There are different types of Joins in MySql. Let’s learn about them.
1. CROSS JOIN
- CROSS JOIN returns the Cartesian product of two tables.
- Every row of table1 is combined with every row of table2.
SELECT columns FROM table1 CROSS JOIN table2;
Example-
Table-A
-
ID Name 1 Raj 2 Siya
Table- B
-
SubID Subject 101 Maths 102 Science
SELECT A.Name, B.Subject
FROM A
CROSS JOIN B;
Output
-
Name Subject Raj Maths Raj Science Siya Maths Siya Science
2. EQUI JOIN
- We extract data from two or more tables on the equality or matching columns, it is called EQUI Join.
- We use the equality (=) sign with where clause to refer equality.
SELECT columns
FROM table1, table2
WHERE table1.column = table2.column;
- Tables: CUSTOMER and ORDERS
SELECT CUSTOMER.Name, ORDERS.Amount
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CustID = ORDERS.CustID;
3. NATURAL JOIN
- Natural Join is also an equal join in which the same name columns from both tables are taken once.
SELECT *
FROM table1
NATURAL JOIN table2;
- Output includes:
EmpID, Name, DeptID, DeptName
(DeptID appears only once)
Summary Table for Quick Revision
-
JOIN Type Condition What it Returns CROSS JOIN No condition All combinations of rows (Cartesian product) EQUI JOIN Uses only “=” Matching rows only (similar to INNER JOIN) NATURAL JOIN Auto based on same column names Matching rows, duplicate columns removed
