Using INTERSECT and INNER JOIN to Find Common Rows in SQL

Query uses INTERSECT in PostgreSQL and INNER JOIN in MySQL

Write a SQL query to find common rows between two queries, using INTERSECT in PostgreSQL and INNER JOIN in MySQL.

Solution:

-- PostgreSQL
SELECT EmployeeID FROM Employees WHERE DepartmentID = 1
INTERSECT
SELECT EmployeeID FROM Employees WHERE Salary > 50000;

-- MySQL
SELECT e1.EmployeeID
FROM Employees e1
JOIN Employees e2 ON e1.EmployeeID = e2.EmployeeID
WHERE e1.DepartmentID = 1 AND e2.Salary > 50000;

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how to find common rows using platform-specific syntax.
• Key Components :
• INTERSECT (PostgreSQL): Finds common rows between two queries.
• INNER JOIN (MySQL): Achieves the same result with different syntax.
• Why Compare Common Row Queries?:
• Syntax differences impact query portability and readability.
• Understanding these differences ensures accurate results.
• Real-World Application :
• In reporting systems, finding common rows supports data reconciliation.

Additional Notes:

• Use INTERSECT in PostgreSQL and INNER JOIN in MySQL for compatibility.
• Test queries on all target platforms to ensure consistent results.
• Important Considerations:
• Optimize joins for performance in large datasets.

For more Practice: Solve these Related Problems:

• Write a SQL query to find common products between two categories, using INTERSECT in PostgreSQL and INNER JOIN in MySQL.
• Write a SQL query to find common customers between two cities, using INTERSECT in PostgreSQL and INNER JOIN in MySQL.
• Write a SQL query to find common orders between two dates, using INTERSECT in PostgreSQL and INNER JOIN in MySQL.
• Write a SQL query to find common employees between two departments, using INTERSECT in PostgreSQL and INNER JOIN in MySQL.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise:Using ARRAY Data Types in PostgreSQL and JSON in MySQL.
Next SQL Exercise: Using RETURNING in PostgreSQL and OUTPUT in SQL Server.