• Data stored in
tables
MySQL • Rows hold on
individual records
JOINS
• Columns hold
A join combines columns from two or more tables
based on a matching condition. different types of
data
• Can create
relationship
VENN
DIAGRAM
Explicit Joins:
Use dedicated keywords like JOIN and the ON clause
to clearly define the joining tables and the matching
conditions between them.
TYPES OF Implicit Joins:
JOINS Don't use explicit join keywords; they rely on the
order of tables mentioned in the FROM clause and
matching column names to infer the join type
Inner Join (Default): Keeps only rows with matching
values in both tables.
Left Join: Keeps all rows from the left table, even if no
match exists in the right table. Null values fill in for
TYPES OF unmatched right columns.
Right Join: Keeps all rows from the right table, filling in
JOINS nulls for unmatched left columns.
Full Outer Join: Includes all rows from both tables,
regardless of matches.
Inner Join :
Retrieves records with matching values in both tables.
Syntax :
INNER JOIN SELECT columns
FROM table1
INNER JOIN table2 ON table1.column_name =
table2.column_name;
Left join :
Retrieves all records from the left table and matching
records from the right table.
Syntax :
LEFT JOIN SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column_name =
table2.column_name;
Right join :
Retrieves all records from the right table and
matching records from the left table.
Syntax:
SELECT columns
RIGHT JOIN FROM table1
RIGHT JOIN table2 ON table1.column_name =
table2.column_name;
Full outer join:
Retrieves all records when there is a match in either
the left or right table.
Syntax:
FULL OUTER
JOIN SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column_name =
table2.column_name;
SELECT column1, column2
FROM table1
WHERE condition1
FULL OUTER UNION ALL
JOIN SELECT column1, column2
FROM table2
WHERE condition2;
Cross join :
A cross join in SQL combines every single row of one
table with every single row of another.
Syntax:
SELECT columns
CROSS JOIN FROM table1
CROSS JOIN table2;
Self join :
Syntax :
SELECT COLUMNS FROM TABLE1 JOIN TABLE2
ON TABLE1. COLUMN =[Link];
SELF JOIN SELECT
C. CUST_NAME AS CUSTOMER_NAME
O.ORDER_ID AS ORDERS_ID
FROM
[Link] LEFT JOIN
[Link]
ON CUSTOMERS.CUSTOMER_ID= ORDERS .CUSTOMER_ID;
Consider two tables, Employees and Departments.
Retrieve the names of employees along with their
department names.
Generate a list showing all combinations of employee
names and department names.
Consider two tables, Employees and Departments.
ASSIGNMEN Retrieve the names of all departments and the names
T of the employees in each department. If a
department has no employees assigned, display "No
Employees" as the employee name.
Provide a query to fetch specific columns from two
tables where they have matching records.
Illustrate a situation where a FULL OUTER JOIN is
necessary and provide a query.
Explanation of joins in SQL and their purpose in
combining data from multiple tables based on a
related column.
ASSIGNMEN Explain the functional difference between Left Join
T and Right Join and when each is used.
Explain the purpose of the ON clause and its role in
specifying the join conditions.
