EXISTS, INTERSECT and EXCEPT operator
In SQL Server 2008, there are several operators that can be used to manipulate data. Three of the most commonly used operators are EXISTS, INTERSECT, and EXCEPT.
EXISTS operator:
- The EXISTS operator is used to check whether a subquery returns any rows. It returns true if the subquery returns at least one row, and false otherwise. The EXISTS operator is typically used in a WHERE clause to filter rows based on the result of a subquery. For example, if you wanted to find all customers who have placed an order, you could use the following SQL statement:
SELECT * FROM Customers WHERE EXISTS (SELECT * FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)
INTERSECT operator:
- The INTERSECT operator is used to return the common rows from two or more SELECT statements. It compares the result sets of two or more SELECT statements and returns only the rows that appear in all of the result sets. For example, if you wanted to find all customers who have placed an order and also have a shipping address in New York, you could use the following SQL statement:
SELECT * FROM Customers WHERE EXISTS (SELECT * FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)
INTERSECT
SELECT * FROM Customers WHERE City = ‘New York’
EXCEPT operator:
- The EXCEPT operator is used to return the rows that appear in the first SELECT statement but not in the second SELECT statement. It compares the result sets of two SELECT statements and returns only the rows that are unique to the first SELECT statement. For example, if you wanted to find all customers who have placed an order but have not yet paid for it, you could use the following SQL statement:
SELECT * FROM Orders WHERE NOT EXISTS (SELECT * FROM Payments WHERE Payments.OrderID = Orders.OrderID)
EXCEPT
SELECT * FROM Orders WHERE Paid = 1 These operators are very useful in manipulating data and allow for complex queries to be written in a concise and efficient manner.
Apply for SQL Server 2008 Certification!!
https://www.vskills.in/certification/certified-sql-server-2008-programmer
