Minus vs intersect in sql

Asked by Stanley Whitaker on
Tags:
9 Answers

Answer by Arturo Harper

The first query's list of columns is used for the final results set. If two corresponding columns in the two queries have different lengths, the first query is used to set the length of the final results set. If the second query's column is longer, you may get an error in your results due to data truncation.,Both queries must have matching lists of columns with matching datatypes. In other words, if your first query returns three columns, a date, a number, and a character column, your second query must also return three columns: a date, a number, and a character column, in that order. The two queries do not need to use the same column names.


SELECT TO_CHAR(LAST_UPDATE_DATE,'YYYY-MM'), 
 UPDATED_BY_USER
FROM PRODUCT
INTERSECT
SELECT TO_CHAR(LAST_UPDATE_DATETIME,'YYYY-MM'), 
 CREATED_BY_USER
FROM PET_CARE_LOG
ORDER BY 1

Source: https://www.relationaldbdesign.com/oracle-sql/module3/union-intersect-minus.php


Answer by Landyn Pace

SET OPERATORS(UNION, UNION ALL, MINUS, INTERSECT),Similar to UNION just that UNION ALL returns also the duplicated values.,It returns a union of two select statements. It is returning unique (distinct) values of them.,When using UNION and UNION ALL columns in SELECT statements need to match. This would return an error:

SELECT * FROM table1
UNION
SELECT * FROM table2;

SELECT * FROM table1
UNION
SELECT * FROM table2;

Source: https://vladimiroracle.wordpress.com/sql/set-operators-union-union-all-minus-intersect/


Answer by Ezequiel Shepard

There are a few things to remember about minus, union and intersection in SQL:,This is the main role of the INTERSECT operator. Let's see how it works.,We should also mention that we can use set operators such as minus, union and intersection in SQL in all types of queries. We don't need to select all of the records from both tables; we can work on the results of existing queries.,Ever heard terms such as union and intersection in SQL? They're examples of set operators, and they come in handy when you need to combine information from multiple tables or queries. In this article, we'll take a closer look at them.

SELECT * FROM BOOKS
UNION
SELECT * FROM MOVIES
SELECT * FROM BOOKS
UNION ALL
SELECT * FROM MOVIES
SELECT * FROM BOOKS
MINUS
SELECT * FROM MOVIES
SELECT * FROM BOOKS
INTERSECT
SELECT * FROM MOVIES

Source: https://learnsql.com/blog/introducing-sql-set-operators-union-union-minus-intersect/


Answer by Kelsey Henderson

The UNION, INTERSECT, and MINUS operators are not valid on LONG columns., The UNION, INTERSECT, and MINUS operators are not valid on LONG columns. ,You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.,You cannot use these operators in SELECT statements containing TABLE collection expressions.

Examples The following query is valid:

SELECT 3 FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;

This is implicitly converted to the following compound query:

SELECT TO_BINARY_FLOAT(3) FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;

The following query returns an error:

SELECT '3' FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;

UNION Example The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:

SELECT location_id, department_name "Department", 
   TO_CHAR(NULL) "Warehouse"  FROM departments
   UNION
   SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name 
   FROM warehouses;

LOCATION_ID Department            Warehouse
----------- --------------------- --------------------------
       1400 IT
       1400                       Southlake, Texas
       1500 Shipping
       1500                       San Francisco
       1600                       New Jersey
       1700 Accounting
       1700 Administration
       1700 Benefits
       1700 Construction
...

UNION ALL Example The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:

SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;

SELECT location_id  FROM locations 
UNION ALL 
SELECT location_id  FROM departments;

INTERSECT Example The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;

MINUS Example The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;

Source: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm


Answer by Ben Acosta

You use this in the same way as union and minus: place it between a select from each table:, Module 9 IntersectThere is also an operator to find the common values: intersect You use this in the same way as union and minus: place it between a select from each table: select colour, shape from your_brick_collection intersect select colour, shape from my_brick_collection; As with minus, the database considers null values to be the same and applies a distinct operator to the results., Module 1 IntroductionThe set operators union, intersect, and minus allow you to combine many tables into one. This tutorial will use these two tables for the queries: select * from my_brick_collection; select * from your_brick_collection;,You can also use distinct on a subset of a table's columns. For example, to get one row for each shape in your collection, select "distinct shape":

Prerequisite SQL

create table my_brick_collection (
  colour varchar2(10),
  shape  varchar2(10),
  weight integer
);

create table your_brick_collection (
  height integer,
  width  integer,
  depth  integer,
  colour varchar2(10),
  shape  varchar2(10)
);

insert into my_brick_collection values ( 'red', 'cube', 10 );
insert into my_brick_collection values ( 'blue', 'cuboid', 8 );
insert into my_brick_collection values ( 'green', 'pyramid', 20 );
insert into my_brick_collection values ( 'green', 'pyramid', 20 );
insert into my_brick_collection values ( null, 'cuboid', 20 );

insert into your_brick_collection values ( 2, 2, 2, 'red', 'cube' );
insert into your_brick_collection values ( 2, 2, 2, 'blue', 'cube' );
insert into your_brick_collection values ( 2, 2, 8, null, 'cuboid' );

commit;

Source: https://livesql.oracle.com/apex/livesql/file/tutorial_GPSAXIY7KMDX4ALZX654OXY6S.html


Answer by Porter Brock

An introduction to the UNION, INTERSECT, and MINUS clauses.,UNION, INTERSECT, and MINUS,An introduction to the POSITION expression.,Previously when we’ve combined tables we have pulled some columns from one and some columns from another and combined the results into the same rows. A UNION stacks all the resulting rows from one table on top of all the resulting rows from another table. The columns used must have the same names and data types in order for UNION to work.

UNION is uncommon because it relies on returning data that could have been stored in the same table. For example, If you wanted to see a list of all of your clients but the international client data was stored in one table and the domestic client data was stored in another, but the structure of the tables was similar, you could write a UNION query to combine the relevant data into one result:

SELECT intl_accounts.account,
       intl_accounts.office_location AS location
  FROM intl_accounts
    UNION
SELECT accounts.account,
       "USA" AS location
  FROM accounts
OFFSET 10

If we wanted to run a query to see only the accounts with both domestic and international offices we would write it with an INTERSECT:

SELECT intl_accounts.account
  FROM intl_accounts
    INTERSECT
SELECT accounts.account
  FROM accounts

In the last example we returned rows that had values in both tables. In order to write a query for all the rows that exist in the first table but not the second, we would use the query:

SELECT intl_accounts.account
  FROM intl_accounts
    MINUS
SELECT accounts.account
  FROM accounts

If we flipped the table order and wrote the query like this:

SELECT accounts.account
  FROM accounts
    MINUS
SELECT intl_accounts.account
  FROM intl_accounts

Source: https://docs.data.world/documentation/sql/concepts/intermediate/UNION_INTERSECT_MINUS.html


Answer by Sutton Spence

This is all about the UNION, MINUS, INTERSECT operators in DBMS. Hope you learned something new today. That's it for this blog.,Here, we first see the 'NOT IN' operator for demonstrating the examples.,Here, we are using the 'IN' clause for demonstrating the examples.,Union All operation is also similar to the union operation. The only difference is that it allows duplicate values in the result set.

The syntax for the union operation is as follows:

SELECT (coloumn_names) from table1 [WHERE condition] UNION SELECT (coloumn_names) from table2 [WHERE condition];

The MySQL query for the union operation can be as follows:

SELECT color_name FROM colors_a UNION SELECT color_name FROM colors_b;

The syntax for the union all operation is as follows:

SELECT (coloumn_names) from table1 [WHERE condition] UNION ALL SELECT (coloumn_names) from table2 [WHERE condition];

The MySQL query for the union all operation can be as follows:

SELECT color_name FROM colors_a UNION ALL SELECT color_name FROM colors_b;

The syntax for the minus operation is as follows:

SELECT (coloumn_names) from table1 [WHERE condition] MINUS SELECT (coloumn_names) from table2 [WHERE condition];

The MySQL query for the minus operation using the 'NOT IN' operator can be as follows:

SELECT color_name FROM colors_a WHERE color_name NOT IN(SELECT color_name FROM colors_b);

The MySQL query for the minus operation using 'JOIN' can be as follows:

SELECT color_name FROM colors_a LEFT JOIN colors_b USING (color_name) WHERE colors_b.color_name IS NULL;

The syntax for the intersection operation is as follows:

SELECT (coloumn_names) from table1[WHERE condition] INTERSECT SELECT (coloumn_names) from table2 [WHERE condition];

The MySQL query for the intersection operation using the 'IN' operator can be as follows:

SELECT color_name FROM colors_a WHERE color_name IN(SELECT color_name FROM colors_b);

Source: https://afteracademy.com/blog/what-are-union-minus-and-intersect-commands-in-dbms


Answer by Gwen Coffey

Set operators can be useful when you want to combine the results of different queries into different rows. In SQL, the different set operators are UNION, UNION ALL, INTERSECT and MINUS (or EXCEPT, depending on your database).,SQL Server: EXCEPT and INTERSECT,This shows all names that are in both the customer and employee table.,There are a few things to keep in mind though.

Set operators are used like this:

SELECT your_select_query
set_operator
SELECT another_select_query;

Let’s see an example based on the sample data above. If we wanted to select from both the employee and customer tables, using UNION, our query would look like this:

SELECT first_name, last_name
FROM customer
UNION
SELECT first_name, last_name
FROM employee;

How can we do that? We can add a static value to each query to indicate which table it came from, or what type of record it is. As long as the number and type of columns match, the query will work.

SELECT ‘Customer’ AS record_type, first_name, last_name
FROM customer
UNION
SELECT ‘Employee’, first_name, last_name
FROM employee;

To order the results, the ORDER BY needs to go at the end.

SELECT ‘Customer’ AS record_type, first_name, last_name
FROM customer
UNION
SELECT ‘Employee’, first_name, last_name
FROM employee
ORDER BY record_type, first_name, last_name;

So, you can add WHERE clauses like this if you need to:

SELECT ‘Customer’ AS record_type, first_name, last_name
FROM customer
WHERE status = ‘Active’
UNION
SELECT ‘Employee’, first_name, last_name
FROM employee
WHERE emp_status = ‘Current’
ORDER BY record_type, first_name, last_name;

If we wanted to JOIN our employee and customer tables, our query might look like this:

SELECT
e.emp_id,
e.first_name,
e.last_name,
c.customer_id,
c.first_name,
c.last_name
FROM employee e
INNER JOIN customer c
ON e.first_name = c.first_name
AND e.last_name = c.last_name;

When we use UNION to combine our tables, it looks like this:

SELECT first_name, last_name
FROM customer
UNION
SELECT first_name, last_name
FROM employee;

Let’s see an example. If we wanted to select from both the employee and customer tables, using UNION ALL, our query would look like this:

SELECT first_name, last_name
FROM customer
UNION ALL
SELECT first_name, last_name
FROM employee;

We can use the same adjustment to the query as we looked at earlier to see which table the data comes from:

SELECT ‘Customer’ AS record_type, first_name, last_name
FROM customer
UNION ALL
SELECT ‘Employee’, first_name, last_name
FROM employee;

Our query would look like this:

SELECT first_name, last_name
FROM customer
MINUS
SELECT first_name, last_name
FROM employee;

Our query would look like this:

SELECT first_name, last_name
FROM customer
INTERSECT
SELECT first_name, last_name
FROM employee;

If we want to order these, we add an ORDER BY at the end.

SELECT first_name, last_name
FROM customer
INTERSECT
SELECT first_name, last_name
FROM employee
ORDER BY first_name, last_name;

If we wanted to see all of the names in all tables, we could write a query like this:

SELECT first_name, last_name
FROM customer
UNION
SELECT first_name, last_name
FROM employee
UNION
SELECT first_name, last_name
FROM referrer
ORDER BY first_name, last_name;

If we wanted to see all records in the customer and employee that weren’t in the referrer table, we can write this query:

SELECT first_name, last_name
FROM customer
UNION ALL
SELECT first_name, last_name
FROM employee
MINUS
SELECT first_name, last_name
FROM referrer
ORDER BY first_name, last_name;

The query would look like this:

SELECT first_name, last_name
FROM customer
UNION ALL
(SELECT first_name, last_name
FROM employee
MINUS
SELECT first_name, last_name
FROM referrer)
ORDER BY first_name, last_name;

Source: https://www.databasestar.com/sql-set-operators/


Answer by Houston Corona

INTERSECT returns distinct rows that are output by both the left and right input queries operator.,EXCEPT returns distinct rows from the left input query that aren't output by the right input query.,INTERSECT Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.,The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

Syntax

{ <query_specification> | ( <query_expression> ) }   
{ EXCEPT | INTERSECT }  
{ <query_specification> | ( <query_expression> ) }  

The following examples show using the INTERSECT and EXCEPT operators. The first query returns all values from the Production.Product table for comparison to the results with INTERSECT and EXCEPT.

-- Uses AdventureWorks  
  
SELECT ProductID   
FROM Production.Product ;  
--Result: 504 Rows  

The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

-- Uses AdventureWorks  
  
SELECT ProductID   
FROM Production.Product  
INTERSECT  
SELECT ProductID   
FROM Production.WorkOrder ;  
--Result: 238 Rows (products that have work orders)  

The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query.

-- Uses AdventureWorks  
  
SELECT ProductID   
FROM Production.Product  
EXCEPT  
SELECT ProductID   
FROM Production.WorkOrder ;  
--Result: 266 Rows (products without work orders)  

The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query. The tables are reversed from the previous example.

-- Uses AdventureWorks  
  
SELECT ProductID   
FROM Production.WorkOrder  
EXCEPT  
SELECT ProductID   
FROM Production.Product ;  
--Result: 0 Rows (work orders without products)  

The following examples show how to use the INTERSECT and EXCEPT operators. The first query returns all values from the FactInternetSales table for comparison to the results with INTERSECT and EXCEPT.

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales;  
--Result: 60398 Rows  

The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales    
INTERSECT   
SELECT CustomerKey   
FROM DimCustomer   
WHERE DimCustomer.Gender = 'F'  
ORDER BY CustomerKey;  
--Result: 9133 Rows (Sales to customers that are female.)  

The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query.

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales    
EXCEPT   
SELECT CustomerKey   
FROM DimCustomer   
WHERE DimCustomer.Gender = 'F'  
ORDER BY CustomerKey;  
--Result: 9351 Rows (Sales to customers that are not female.)  

Source: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql


Related