A set operator in SQL is a keyword that is used to combine common results from both queries into a single set. Sometimes, when working with SQL server, you have to work on multiple tables together. Instead of combining both tables, you may combine common values to make it little more relevant, and it is done through set operators in SQL. There are different types of set operators that are used by vendors, and you can pick any one of them as per your requirements. The frequently used set operators are:
Let’s take a look at each of these. Here, our main focus is to discuss the Intersect operator in detail, and rest will be discussed in brief. Take a look at how to use set operators in SQL. Here is the basic syntax for your reference:
SELECT your_select_query Set_operator SELECT another_select_query;
It uses two or more select queries with a set operator in between. There are a few things to consider when working with set operators in SQL. When selecting columns, the number of columns should be matched between queries and data types should always be compatible. For example, if you have taken three columns for the first query, then it should be 3 for the second query as well. The data type should also be the same.
For example, if you are using the character data type for the first query, then it should be the same for another as well. Also, if you want to arrange the final set in order then use the Order by clause at the end of query before the set operator.
The Intersect operator is used to combine similar rows from two queries. It returns rows that are common between both results. To use Intersect operator in SQL, both queries should use the same length for columns and it must-have compatible data types too. Let us understand the concept with the help of a simple example.
Here, we have to work with two queries. The Orange circle is the first query, and the Blue circle is the second query. Now, use the Intersect operator and combine these two queries. A common green area of two circles is the final result after using intersect operator keeping only rows in common to both queries. It is very much similar to the UNION operator, and we can use the same rules for both operators, but MYSQL does not support the intersect operator. So, it can be used with SQL Server only. The Venn Diagram for Intersect Operator can be given as:
SELECT column1 [,column2 ] FROM table1 [,table2 ] [WHERE coondition] INTERSECT SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
Here, the given condition can be any expression based on the requirement. Add the table names from which you want to retrieve the data. Where the condition is optional, and it is generally used to make your search very specific.
Points to Remember: Keep in mind that there should be the same number of expressions in both SELECT statements. Also, expressions should have the same data type that you want to club together.
Here is a simple example with a single expression and the same data types.
SELECT supplier_id FROM suppliers INTERSECT SELECT supplier_id FROM orders;
Here, Supplier ID is the common column in both the tables, suppliers, and orders. The same will appear in the result set too. Now, let us complicate the example using WHERE condition to the Intersect Query. Let us see how can we add where condition to the given an example.
SELECT supplier_id FROM suppliers WHERE supplier_id > 78 INTERSECT SELECT supplier_id FROM orders WHERE quantity <> 0;
Here, where the condition is added to both expressions. For the first expressions, it will pick records having supplier ID greater than 78. For the second expression, it will select rows from Orders table whose quantity is not equal to Zero. In this way, where the condition is useful when you want to pick highly specific data.
Now let us look at how to use SQL Intersect operator to return more than one column. For example:
SELECT contact_id, last_name, first_name FROM contacts WHERE last_name <> 'Anderson' INTERSECT SELECT customer_id, last_name, first_name FROM customers WHERE customer_id < 50;
In this way, you can use multiple expressions together and set the where condition to make the search more specific and relevant.
Here is an example of SQL Intersect Operator where Order By clause has been used.
SELECT supplier_id, supplier_name FROM suppliers WHERE supplier_id > 2000 INTERSECT SELECT company_id, company_name FROM companies WHERE companny_id > 1000 Order by 2;
Since column names are different for both the tables, it would be great using Order by clause here. In the above example, we have sorted results by arranging columns in ascending order. You have to be little logical when working with multiple operators or clauses together otherwise it may result in errors in the end.
The intersect operator is good when you want to find two common rows between two results. The Intersect operator is highly similar to the AND operator, and they usually work on different database objects. The “Intersect” operator is used to combine entire rows while “AND” operator is used to combine columns within rows. Let us understand both operators with the help of an example below. Here is one query for makes and other query is for females.
SELECT Jobtitle FROM HumanResources.Employee WHERE Gender ='M'
To finish, we have to find out titles in common, and it can be quickly done by Intersect operator.
SELECT Jobtitle FROM HumanResources.Employee WHERE Gender ='M' INTERSECT SELECT Jobtitle FROM HumanResources.Employee WHERE Gender ='F'
The same example can be written using AND operator too. Let us see how the above code looks like when an AND operator replaces intersect operator.
SELECT Jobtitle FROM HumanResources.Employee WHERE Gender ='M' AND Gender ='F'
It looks much simpler, but it won’t work because the where clause is evaluated for each row, and you are never going to find a Gender value equal for both male and female expressions.
The Intersection operator was not a part of SQL server since the time of its inception, but it was introduced later. Before Intersection operator, the inner join clause was used for the same purpose. Below is the equivalent statement to find the common job title in both queries.
SELECT DISTINCT M.JobTitle FROM HumanResources.Employee As M Inner JOIN HumanResources.Employee AS F On M.JobTitle = F.Jobtitle AND M.Gender ='M' AND F.Gender = 'F'
It is called the self-join where we are joining table to itself to match up job titles with the same values. Keep in mind that Null is not the value; it will always return False. The major difference between Inner join and Intersect operators is that Inner join does not consider Null values while Intersect operator does match Nulls.
The union operator is used to combine results from two queries; it removes duplicate values too from the table. Here is a Venn Diagram for the Union Operator where each circle is a query result.
Here is the basic syntax for the UNION operator:
FROM first_name, last_name FROM customer Union SELECT first_name, last_name FROM employee;
The UNION and JOIN work similar to a single objective of joining two tables together. The difference is, how are they joining the final set. UNION combines data in separate rows while JOIN combines data in separate columns.
This operator also combines the result from two queries. It works similar to the UNION operator, but it does not work on duplicate values. Let us see the Venn diagram for the UNION ALL operator:
The basic syntax for UNION ALL operator can be given as:
FROM first_name, last_name FROM customer UNION ALL SELECT first_name, last_name FROM employee;
How are UNION and UNION ALL operators different?
The major difference between the two operators is that the UNION operator removes duplicate values while UNION ALL does not work on duplicate values. UNION operator performs a DISTINCT operation on the result set to eliminate duplicates. Keep in mind that ALL means you want to combine all records.
As a result, UNION is slower than UNION ALL operator and little costlier too. UNION performs distinct operator that takes time and resources while there is no DISTINCT operator in the case of UNION ALL operator. So, if you are sure that rows are unique in both the tables, then you should try UNION ALL in that case instead of UNION.
Another common set operator in Minus. It will find the result present in the first query bit not available in the second query. The Venn diagram for Minus operator can be given as:
The basic syntax for this operator is given below.
FROM first_name, last_name FROM customer MINUS SELECT first_name, last_name FROM employee;
The except operator is another popular set operator that is frequently used with SQL code lines. It works similar to MINUS showing result from one query that doesn’t exist in another query. However, MINUS is an Oracle-specific keyword while Except keywords can be used other databases like SQL Server. So, if you see EXCEPT operator somewhere then keep in mind that it is similar to MINUS but for a different database. The Venn diagram for the EXCEPT operator can be given as:
With this discussion, we come to the end of our blog where we learned different set operators keeping Intersect operator in focus. You can use them with your database when you want to combine two or more queries. Don’t forget to follow tips that we have discussed in the blog, or the final result set can be redundant. If you are still not sure, join the SQL Server Certification course with us and learn using each of the operators practically. All the Best for practical learning to SQL Set Operators with JanBask Training.
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Receive Latest Materials and Offers on SQL Server Course