Month End Sale : Flat 30% off ON LIVE CLASSES + 2 FREE SELF-PACED COURSES AS A BONUS! - SCHEDULE CALL
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 a 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. Here, we will majorly discuss intersect SQL operator with example and Minus SQL operator.
The frequently used set operators are:
Let’s take a look at each of these. Here, our main focus is to discuss the Intersect SQL operator in detail, and the 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 the query before the set operator.
The Intersect SQL 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 the 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 in SQL 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.
Learn SQL Server in the Easiest Way
Here is a simple example for Intersect operator in SQL with a single expression and the same data types.
SELECT supplier_id FROM suppliers INTERSECT SELECT supplier_id FROM orders;
Here, the 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 we can add where condition to the given 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 the 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
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.
Request for a demo class now to know all about SQL operators.
SQL Server Training & Certification
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 the “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 an 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 what the above code looks like when an AND operator replaces the 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 the SQL server since the time of its inception, but it was introduced later. Before the 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 a table 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 similarly 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;
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 a little costlier too. UNION performs distinct operators that take 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 SQL. The Minus in SQL Server will find the result present in the first query bit not available in the second query. The Venn diagram for Minus SQL 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 similarly to MINUS showing results from one query that doesn’t exist in another query. However, MINUS is an Oracle-specific keyword while Except keywords can be used in 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:
Sign up for online SQL training classes today to master the SQL skills.
SQL Server Training & Certification
With this discussion, we come to the end of our blog where we learned different set operators keeping SQL Intersect operator and Minus SQL operator in focus. You can use Intersect SQL operator with your database when you want to combine two or more queries. Don’t forget to follow the tips that we have discussed in the SQL Intersect 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.FaceBook Twitter Google+ LinkedIn Pinterest Email
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.
MS SQL Server
Receive Latest Materials and Offers on SQL Server Course