JOINs (INNER, LEFT , RIGHT & CROSS JOIN) | SQL - MYSQL | Belayet Hossain

JOINs in SQL

JOINs are used in SQL to combine rows from two or more tables based on a related column between them. Here’s an overview of the common JOIN types:

(INNER JOIN, LEFT JOIN, RIGHT JOIN & CROSS JOIN)

→ N.B: We need to ensure the same data type for both columns in both tables when selecting the common column

?? 1. INNER JOIN

• Purpose: Returns rows that have matching values in both tables.
• Key Point: Non-matching rows are excluded.

→ Syntax:

select column1, column2, column3....

from table1

inner join table2

on table1.column_name = table2.column_name ;

→ Example: Find the department name of all employees using 'Employee' and 'department' tables and Return the employee ID, Name & department of each employee's in the output table

→ Data Table:

→ Query:

→ Output:

??2. LEFT JOIN:

• Purpose: Returns all rows from the left table and the matching rows from the right table. Non-matching rows from the right table are replaced with NULL.
• Key Point: Includes all rows from the left table.

Note: The records that do not match with the table on the right side will show the column value "NULL" for that record.

→ Syntax:

select column1, column2, column3....

from table1

left join table2

on table1.column_name = table2.column_name ;

→ Example: Find the district name of all employees using 'Employee' and 'district' tables and Return the employee ID, Name, salary & department of each employee's in the output table.

→ Data Table:

→ Query:

→ Output:

→Reasons of NULL value: The District column value is NULL for employee_id 3, because employee_id 3 is not available in the District table

??3. RIGHT JOIN:

• Purpose: Returns all rows from the right table and the matching rows from the left table. Non-matching rows from the left table are replaced with NULL.
• Key Point: Includes all rows from the right table.

Note: The records that do not match the table on the left side will show the column value "NULL" for that record.

→ Syntax:

select column1, column2, column3....

from table1

right join table2

on table1.column_name = table2.column_name ;

→ Example: Find the district name of all employees using 'Employee' and 'district' tables and Return the employee ID, Name, salary & department of each employee's in the output table.

→ Data Table:

→ Query:

→ Output:

→Reasons of NULL value: The column of employee_id, employee_name & salary values are NULL for district 'Barishal' which employee_id is 5 in the Disctrict Table, and employee_id 5 is not available in the employee table

??4. CROSS JOIN

• Purpose: Returns the Cartesian product of both tables (i.e., every row from the first table is paired with every row from the second table).
• Key Point: No condition is required.

→ Syntax:

select column1, column2, column3...

from table1

cross join table2 ;

→ Example: Use the 'student_info' & 'student_department' tables. Return the student_id, Name & department for all student in the output table.

→ Data Table:

→ Query :

→ Output:

Result: Every students paired with every department

#dataanalytics #dataanlysis #SQL #MySQL #powerbi #joins #InnerJoin #LeftJoin #RightJoin #crossJoin #career #tech #hiring #recruitment #data