Set Operators Explained with Interview Questions |SQL part5|

Hello viewers, Welcome to DataToolkitt!
Thank you for visiting my channel. Here you will find free technical tutorials to help you learn the latest technologies and improve your skills.
I keep my videos simple, clear, and easy to understand.
If you find them helpful, subscribe, press the bell icon, and like the videos!
In this video we have covered set operator with interview questions.
select * from employees_2023;
select * from employees_2024;
--1.Write a query to find all unique job titles from two employee tables using a set operator.
--2.Difference between union and union all?
SELECT job_title FROM employees_2023
UNION all
SELECT job_title FROM employees_2024;
--3.Can you use ORDER BY with set operators? If yes, where?
--what will happen if i used order by in both select statment
--Ans:ORDER BY is allowed only at the end of the entire query.
SELECT emp_name, department FROM employees_2023
UNION
SELECT emp_name, department FROM employees_2024
ORDER BY emp_name;
--4.What below query will return?
--Write a query to get employees who are present in both tables.
--Ans:Returns only employees present in both years.
SELECT emp_name FROM employees_2023
INTERSECT
SELECT emp_name FROM employees_2024;
--5.Present in 2023 but not in 2024 or Employees who left after 2023.
--Write a query to get employees who are in Table A but not in Table B.
SELECT emp_name FROM employees_2023
MINUS
SELECT emp_name FROM employees_2024;
--6.Employees who joined in 2024.
SELECT emp_name FROM employees_2024
MINUS
SELECT emp_name FROM employees_2023;

--7.What are the data type requirements for columns in set operators?
--Ans:Valid: Same number of columns, same or compatible data types
--8.What happens if column names are different in both queries? Which name appears in the result?
--Why?
--Ans:The database only uses the metadata from the first query to label the output.
SELECT emp_name AS name_2023 FROM employees_2023
UNION
SELECT emp_name AS name_2024 FROM employees_2024;
--9.How are you handling NULL in set operator?
--Ans:NULL is treated as a valid value and considered the same in duplicates.
--Set operators treat NULL as a valid value for comparison, but:
--UNION treats two NULLs as duplicates (only one will remain).
--UNION ALL keeps all duplicates, even if they’re NULL.
--INTERSECT considers NULL = NULL (so NULL can appear in the result if it exists in both sets).
--MINUS removes NULL rows if they appear in both sets.
SELECT NULL AS emp_name FROM dual
UNION
SELECT emp_name FROM employees_2024;
-- Even though NULL is unknown, SQL set operators still use it for duplicate checking.
#sql #sql #learnsql #techskills
#MySQL
#sqlcourse
#sqlforbeginners
#sqlserver
#top
#sqlqueries
#sqloperators
#setoperators
#sqlsetoperators