Joins.pptxjjbmmmnnnnnjjjxrhjfluflurulrdudlu

Lchdhxlh

1. DBMS Joins in Relational Algebra Understanding Inner and Outer Joins

2. Natural Join Natural Join combines the tables based on columns with the same name and data type . Automatically matches common columns between tables. SQL : SELECT * FROM Employees NATURAL JOIN Departments; PQL : Employees ⋈ Departments

3. emp_id name dept_id 101 Alice 1 102 Bob 2 103 Charlie 1 104 David 3 105 Eva NULL dept_id dept_name 1 HR 2 Engineering 3 Marketing 4 Finance Example - Natural Join emp_id name dept_id dept_name 101 Alice 1 HR 102 Bob 2 Engineering 103 Charlie 1 HR 104 David 3 Marketing Employees ⋈ Departments Employees Departments

4. Theta Join Theta Join combines the tables based on a specified condition (not necessarily equality). Conditional Operators incudes: >,<, <=, >= etc. SQL – SELECT * FROM Employees E, Departments D WHERE E.dept_id < D.dept_id ; PQL- Employees⋈ ( E.dept_id < D.dept_id ) Departments

5. emp_id name dept_id 101 Alice 1 102 Bob 2 103 Charlie 1 105 Eva NULL dept_id dept_name 1 HR 2 Engineering 3 Marketing 4 Finance Example - Theta Join Employees ⋈( E.dept_id < D.dept_id ) Departments Employees Departments emp_id name dept_id dept_id dept_name 101 Alice 1 2 Engineering 101 Alice 1 3 Marketing 101 Alice 1 4 Finance 102 Bob 2 3 Marketing 102 Bob 2 4 Finance 103 Charlie 1 2 Engineering 103 Charlie 1 3 Marketing 103 Charlie 1 4 Finance

6. Equi Join Joins tables based on equality between specified columns. SQL - SELECT * FROM Employees E, Departments D WHERE E.dept_id = D.dept_id ; PQL- Employees⋈ ( E.dept_id = D.dept_id ) Departments

7. dept_id dept_name 1 HR 2 Engineering 3 Marketing 4 Finance Example - Equi Join Employees ⋈( E.dept_id = D.dept_id ) Departments Employees Departments emp_id name dept_id dept_id dept_name 101 Alice 1 1 HR 102 Bob 2 2 Engineering 103 Charlie 1 1 HR 104 David 3 3 Marketing emp_id name dept_id 101 Alice 1 102 Bob 2 103 Charlie 1 104 David 3 105 Eva NULL

8. Outer Joins Outer Joins Returns matching rows plus unmatched rows from one or both tables. Types: 1. Left Outer Join 2. Right Outer Join 3. Full Outer Join

9. Left Outer Join It r eturns all rows from the left table and matching rows from the right table. SQL - SELECT * FROM Employees E LEFT OUTER JOIN Departments D ON E.dept_id = D.dept_id ; PQL- π *​(Employees ⟕ ( E.dept_id = D.dept_id )​ Departments)

10. dept_id dept_name 1 HR 2 Engineering 3 Marketing 4 Finance Example – Left Outer Join Employees ⟕ ( E.dept_id = D.dept_id ) Departments Employees Departments emp_id name dept_id 101 Alice 1 102 Bob 2 103 Charlie 1 104 David 3 105 Eva NULL emp_id name dept_id dept_id dept_name 101 Alice 1 1 HR 102 Bob 2 2 Engineering 103 Charlie 1 1 HR 104 David 3 3 Marketing 105 Eva NULL NULL NULL

11. Right Outer Join It r eturns all rows from the right table and matching rows from the left table. SQL - SELECT * FROM Employees E RIGHT OUTER JOIN Departments D ON E.dept_id = D.dept_id ; PQL- π * ​(Employees ⟖ ( E.dept_id = D.dept_id ) ​Departments)

12. dept_id dept_name 1 HR 2 Engineering 3 Marketing 4 Finance Example – Right Outer Join Employees ⟖ ( E.dept_id = D.dept_id ) Departments Employees Departments emp_id name dept_id 101 Alice 1 102 Bob 2 103 Charlie 1 104 David 3 105 Eva NULL emp_id name dept_id dept_id dept_name 101 Alice 1 1 HR 103 Charlie 1 1 HR 102 Bob 2 2 Engineering 104 David 3 3 Marketing NULL NULL NULL 4 Finance

13. Full Outer Join It r eturns all rows when there's a match in either the left or right table. SQL – SELECT * FROM Employees E FULL OUTER JOIN Departments D ON E.dept_id = D.dept_id ; PQL – π *(Employees ⟗ ( E.dept_id = D.dept_id ) ​Departments)

14. dept_id dept_name 1 HR 2 Engineering 3 Marketing 4 Finance Example – Full Outer Join Employees ⟗ ( E.dept_id = D.dept_id ) Departments Employees Departments emp_id name dept_id 101 Alice 1 102 Bob 2 103 Charlie 1 104 David 3 105 Eva NULL emp_id name dept_id dept_name 101 Alice 1 HR 102 Bob 2 Engineering 103 Charlie 1 HR 104 David 3 Marketing 105 Eva NULL NULL NULL NULL 4 Finance

15. Join Type Definition Includes Matching Rows? Includes Non-Matching Rows? Natural Join Joins based on common column(s) with the same name and data type. Yes No Theta Join Joins based on a condition other than equality (e.g., <, >, !=). Yes No Equi Join Joins based on equality between specific columns. Yes No Left Outer Join Returns all rows from the left table, plus matching rows from the right table. Non-matching right table values are filled with NULL. Yes Left table only Right Outer Join Returns all rows from the right table, plus matching rows from the left table. Non-matching left table values are filled with NULL. Yes Right table only Full Outer Join Returns all rows when there is a match in either table. Non-matching values from both tables are filled with NULL. Yes Both tables

16. Thank You