Interview Questions and Answers on Joins part4|

Welcome to Data Toolkitt! This channel is dedicated to helping data engineers master essential tools and techniques, from ETL processes to cloud platforms and big data technologies. #sql #sqljoins
#databasemanagement #DBMS#techtutorials#sqlforbeginner #sqltutorial#sqlqueries
In this video, you will learn everything about SQL JOINS
Types of JOINS (INNER, LEFT, RIGHT, FULL, CROSS)
Anti JOINS & Non-Anti JOINS
How to fetch the count of records for each JOIN type
Real-time examples to make concepts crystal clear
--1. When would you use a LEFT JOIN instead of INNER JOIN?
--Use LEFT JOIN when you want all records from the left table, even if there's no match in the right table.
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees_join e
INNER JOIN departments_join d ON e.dept_id = d.dept_id;

SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees_join e
LEFT JOIN departments_join d ON e.dept_id = d.dept_id;

SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees_join e
RIGHT JOIN departments_join d ON e.dept_id = d.dept_id;

--2.What happens when there is no match in LEFT JOIN?
--How to get employees without department
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees_join e
LEFT JOIN departments_join d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

--4.Why LEFT JOIN returns more rows than INNER JOIN?
SELECT COUNT(*) FROM employees_join e
LEFT JOIN departments_join d ON e.dept_id = d.dept_id;

SELECT COUNT(*) FROM employees_join e
INNER JOIN departments_join d ON e.dept_id = d.dept_id;

--5.What happens if we forget the ON clause in a JOIN?
SELECT * FROM employees_join e JOIN departments_join d;

--6.why do we use UNION for FULL OUTER JOIN in some cases?
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees_join e
LEFT JOIN departments_join d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees_join e
RIGHT JOIN departments_join d ON e.dept_id = d.dept_id;

SELECT
e.emp_name AS EmployeeName,
d.dept_name AS DepartmentName
FROM employees_join e
FULL OUTER JOIN departments_join d
ON e.dept_id = d.dept_id
ORDER BY e.emp_name;

--7. Which JOIN returns the most rows?
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees_join e
CROSS JOIN departments_join d;

--8.Which join is default join?
SELECT * FROM employees_join e JOIN departments_join d ON e.dept_id = d.dept_id;

--9.join vs union
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees_join e
JOIN departments_join d ON e.dept_id = d.dept_id;

SELECT emp_id, emp_name, NULL as dept_name FROM employees_join
UNION
SELECT NULL, NULL, dept_name FROM departments_join;

--10.How do NULL values affect JOINs?
--NULL values do not match each other. This means NULL = NULL returns false in a JOIN condition.
--In INNER JOINs, such rows are excluded. In OUTER JOINs, they appear with NULLs from the unmatched table.
--NULL in Amit's dept_id won't join with anything.