21st Apr 2021 9 minutes read What Do the SQL INTERSECT and MINUS Clauses Do? Andrew Bone sql learn sql SQL clause Table of Contents SQL INTERSECT SQL MINUS MINUS vs. INTERSECT: Examples More SQL INTERSECT and MINUS Examples One Table is a Subset of the Other Table’s Data Both Tables Have the Same Data One Table Contains No Data INTERSECT, MINUS and More SQL Set Operators Do you know the difference between SQL’s INTERSECT and MINUS clauses and how to use them? You will find examples and explanations in this article. SQL INTERSECT and MINUS are useful clauses for quickly finding the difference between two tables and finding the rows they share. INTERSECT compares the data between tables and returns only the rows of data that exist in both tables. MINUS compares the data between tables and returns the rows of data that exist only in the first table you specify. Both SQL INTERSECT and MINUS (or EXCEPT, depending on your SQL dialect) form part of LearnSQL.com’s SQL Basics course. SQL INTERSECT The SQL INTERSECT operator is used to return the results of two or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results. The number and order of the columns must be the same in all of the SELECT queries. The column data types must be the same, or at least compatible with one another. INTERSECT filters duplicates and returns only distinct rows that are common between all of the queries. Here is the syntax for the INTERSECT operator: SELECT column_1 [, column_2, …, column_n] FROM table_1 [, table_2, …, table_n] [WHERE condition] INTERSECT SELECT column_1 [, column_2, …, column_n] FROM table_1 [, table_2, …, table_n] [WHERE condition] Anything inside the square brackets is entirely optional. The concept of an INTERSECT is further explained by the following diagram: The INTERSECT query will return the records in the shaded area. These are the records that exist in both data sets INTERSECT is just one way of merging the results of different SQL queries. If you’re interested in learning more, this article covers the different methods for combining the results of SQL queries. SQL MINUS The SQL MINUS clause is used to combine two SELECT statements, but it returns rows from the first SELECT statement that are not returned by the second SELECT statement. SQL MINUS only returns rows that are not available in the second SELECT statement. Each SELECT statement within a MINUS query must contain the same number of fields in the result sets along with similar data types. The MINUS operator is not supported in all SQL databases. It can be used in databases like MySQL and Oracle. For databases like SQL Server, PostgreSQL, and SQLite, use the EXCEPT operator to perform this type of query. SELECT column_1 [, column_2, …, column_n] FROM table_1 [, table_2, …, table_n] [WHERE condition] MINUS SELECT column_1 [, column_2, …, column_n] FROM table_1 [, table_2, …, table_n] [WHERE condition] The SQL code shown in the square brackets is entirely optional. The concept of SQL MINUS is further explained by this diagram: The MINUS query will return the records in the red area. These are the records that exist in the first dataset and not the second. MINUS vs. INTERSECT: Examples Let’s apply the INTERSECT and MINUS clauses to a practical example. Imagine we have the following tables. customers - Contains details about our customers idcustomer_namecountry 1Infotech SolutionsGermany 2Corpway IndustriesIreland 3Fenway IncEngland 4Fairview LtdFrance suppliers - Contains details about our suppliers. idcustomer_namecountry 1Carbon Way SuppliersSpain 2Alloy IncFrance 3Materials Delivered LtdIreland 4Concrete CrewPoland 5Conglorito SystemsItaly Now let’s write an INTERSECT query. We want to find the countries that our suppliers and customers have in common. SELECT country FROM customers INTERSECT SELECT country FROM suppliers We specify the country column in each SELECT clause. Executing this query results in the following data set: country France Ireland Looking back over the customers and suppliers tables, we can see this result is correct. Only the countries of France and Ireland are shared between the tables. Now let’s apply the MINUS operator to the same tables. This will allow us to get the countries in our customers table that are not in our suppliers table: SELECT country FROM customers MINUS SELECT country FROM suppliers Executing this query yields the result: country England Germany There we have it: the countries that are unique to our customers table. The order of your SELECT clauses is very important here, and it is something you must be mindful of when using the MINUS operator. Let’s reverse the order of our SELECT clauses and see what happens. SELECT country FROM suppliers EXCEPT SELECT country FROM customers Executing this query returns the following data: country Italy Poland Spain As you can see, our result set was wildly different. SQL starts with our suppliers table and then removes any countries that exist in the customers table. If you feel overwhelmed, consider trying the SQL Fundamentals track from LearnSQL.com, which will provide you with a solid SQL foundation. It will teach you basic SQL statements like WHERE, GROUP BY, ORDER BY, and HAVING. You’ll also learn how to JOIN tables and add, modify, or remove data from a database. This was a simple example showing you how the INTERSECT and MINUS operators can be used to quickly retrieve distinct datasets. Let us look at some more examples that show you how these operators will act in three different scenarios: One table is the subset of the other table's data. Both tables have the same data. One table in your query contains no data. More SQL INTERSECT and MINUS Examples One Table is a Subset of the Other Table’s Data For this scenario, imagine we have two tables called employees and planning_committee. As you can see the planning_committee table is a subset of employees, meaning all of its data is also contained in employees. employees - All of the employees employed at our company. employee_idfirst_namelast_name 321873JohnSmith 415938JaneRamsey 783273AndrewJohnson 832923ChristinaGrey planning_committee - All of the employees on our company’s planning committee. employee_idfirst_namelast_name 415938JaneRamsey 783273AndrewJohnson Let’s see how the INTERSECT clause behaves in this scenario. SELECT employee_id, first_name, last_name FROM employees INTERSECT SELECT employee_id, first_name, last_name FROM planning_committee The following dataset is returned: employee_idfirst_namelast_name 415938JaneRamsey 783273AndrewJohnson As you can see, only the subset is returned. This is because the planning_committee table is a subset of the employees table; thus, the result will be simply the planning_committee table. What happens if we use the MINUS clause instead? Imagine we wanted to find all of the employees that were not on the planning committee. This can be achieved by writing the query below: SELECT employee_id, first_name, last_name FROM employees MINUS SELECT employee_id, first_name, last_name FROM planning_committee Executing this query yields the following result: employee_idfirst_namelast_name 321873JohnSmith 832923ChristinaGrey You can see that these employees are not in the planning_committee table; this is the desired result! Again, the ordering of the tables here is important. If we were to reverse the order of the SELECT clauses like so ... SELECT employee_id, first_name, last_name FROM planning_committee EXCEPT SELECT employee_id, first_name, last_name FROM employees … executing this query would yield a very different result: employee_idfirst_namelast_name Since all of the data in the planning_committee table is contained in the employees table, nothing gets returned. SQL MINUS only returns distinct data. Time to look at our next scenario. Both Tables Have the Same Data There may be a situation where two SQL tables have identical data. How do the INTERSECT and MINUS clauses handle this situation, and what results should you expect? Let’s find out! For this scenario, we will use the following tables: payroll - All employees currently on the payroll at our company. employee_idfirst_namelast_name 321873JohnSmith 415938JaneRamsey 783273AndrewJohnson 832923ChristinaGrey employees - All employees at our company. employee_idfirst_namelast_name 321873JohnSmith 415938JaneRamsey 783273AndrewJohnson 832923ChristinaGrey You can see that all of the employees at our company are currently on the payroll and are getting paid as they should. This results in these tables containing identical data. Let’s look at how the INTERSECT clause handles this case: SELECT employee_id, first_name, last_name FROM employees INTERSECT SELECT employee_id, first_name, last_name FROM payroll Executing this query returns this result: employee_idfirst_namelast_name 321873JohnSmith 415938JaneRamsey 783273AndrewJohnson 832923ChristinaGrey Since all of the data was shared between the two tables, everything gets returned! Time to see how the MINUS clause handles tables that share identical data: SELECT employee_id, first_name, last_name FROM employees MINUS SELECT employee_id, first_name, last_name FROM payroll Executing this query shows the following result: employee_idfirst_namelast_name No data is returned! SQL starts by selecting the data in our employees table and then subtracts the data that exists in the payroll table. In this case, everything gets removed. This leads to our final scenario. What if one of the tables that makes up part of an INTERSECT or MINUS clause contains no data? One Table Contains No Data For this scenario, we will use the following tables: employees - All of the employees employed at our company. employee_idfirst_namelast_name 321873JohnSmith 415938JaneRamsey 783273AndrewJohnson 832923ChristinaGrey on_vacation - All our company employees currently on vacation. employee_idfirst_namelast_name Let’s find out how the INTERSECT clause handles an empty table: SELECT employee_id, first_name, last_name FROM employees INTERSECT SELECT employee_id, first_name, last_name FROM on_vacation We’re given the following result after executing this query: employee_idfirst_namelast_name No results! When we use an empty table as part of the INTERSECT clause, we will get an empty dataset. This is because no matches could be found between the two tables. How the MINUS clause is affected by the inclusion of an empty table is entirely dependent on the order you specify. For example, this query ... SELECT employee_id, first_name, last_name FROM employees MINUS SELECT employee_id, first_name, last_name FROM on_vacation ... yields the following result: employee_idfirst_namelast_name 321873JohnSmith 415938JaneRamsey 783273AndrewJohnson 832923ChristinaGrey The MINUS clause here has very little effect, as you are essentially taking away nothing (an empty data set) from the employees table. However, if we were to reverse the order of the SELECT clauses, like so ... SELECT employee_id, first_name, last_name FROM on_vacation MINUS SELECT employee_id, first_name, last_name FROM employees … we’re faced with a very different result: employee_idfirst_namelast_name Smith Another empty data set! This occurs because the first SELECT clause retrieves the data from the on_vacation table, which in this case is nothing. We then instruct SQL to take away the employees data from our empty data set. This has no effect, as the data set is already empty! INTERSECT, MINUS and More SQL Set Operators In this article, we have covered SQL INTERSECT and MINUS in great detail. These are known as SQL set operators, which also include UNION and UNION ALL. You can read an introduction to SQL set operators here, along with how to further refine results with set operators. Tags: sql learn sql SQL clause