Interview Questions and Answers on Joins part4|#sql #join

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.