CODEWITHSUNDEEP
mysqlsql
Antonio1996
Antonio1996

Reputation: 756

Alternative to except in MySQL

I must write a Query like this in MySQL:

SELECT * 
FROM Tab1
EXCEPT
SELECT * 
FROM Tab1 
WHERE int_attribute_of_Tab1>0

but MySQL doesn't support the keyword EXCEPT. Is there a standard mode to use correctly another operator that simulate the except in MySQL?

Upvotes: 38

Views: 71906

Answers (7)

chandan satapathy
chandan satapathy

Reputation: 11

I came across this solution but it takes into consideration all the fields and wont be a simple select *.

Suppose you have field id,name, att1, att2, fin_year in one table tbl_tab which has 50 records of fin_year =2022 and 25 record of fin_year =2023

Now to get all the data from tab1 for fin_year 2022 except fin_year 20233 the following needs to be done.

select * from tbl_tab 
where fin_year=2022 and id not in (
select t1.id 
from tbl_tab t1 
inner join tbl_tab t2 
   on t2.name=t1.name
   and t2.att1=t1.att1
   and t2.att2=t1.att2
where t1.fin_year=2022
and t2.fin_year=2023);

This is equivalent of

select name, att1, att2, fin_year from tbl_tab where fin_year=2022
except 
select name , att1, att2, fin_year from tbl_tab where fin_year=2023;

Upvotes: 0

lemon
lemon

Reputation: 15482

Since MySQL version 8.0.31 update, the EXCEPT operator has become available to use in this DBMS. If you are allowed to update your MySQL version, you are free to use the notation:

SELECT * FROM Tab1
EXCEPT
SELECT * FROM Tab1 
WHERE int_attribute_of_Tab1>0

Upvotes: 3

Sukhbir
Sukhbir

Reputation: 21

You can use multiple NOT IN operators combined with AND operators for multiple columns. For example:

SELECT col1, col2 FROM table1 WHERE col1 NOT IN(SELECT col1 FROM table2) AND col2 NOT IN(SELECT col2 FROM table2)...;

Upvotes: 2

LukStorms
LukStorms

Reputation: 29657

If Tab1 has a primary key (f.e. ID) then you could use a NOT EXISTS to itself like this :

SELECT * 
FROM Tab1 AS t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM Tab1 AS t2
    WHERE t2.ID = t1.ID
      AND t2.int_attribute_of_Tab1 > 0
)

But it's kinda pointless in this case.
And it's not what an EXCEPT/MINUS tries to do.
(excluding identical rows)

The question's query with the EXCEPT uses the same table twice.
So reversing that WHERE criteria on Tab1 would give the same results.

SELECT * 
FROM Tab1 
WHERE (int_attribute_of_Tab1 <= 0 OR int_attribute_of_Tab1 IS NULL)

If it were 2 different tables then this

SELECT t1col1, t1col2, t1col3
FROM Table1
EXCEPT
SELECT t2col4, t2col5, t2col6
FROM Table2
WHERE int_attribute_of_Tab1 > 0

Could be replaced by comparing each selected column

SELECT DISTINCT t1col1, t1col2, t1col3
FROM Table1 AS t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM Table1 AS t2
    WHERE t2.t2col4 = t1.t1col1
      AND t2.t2col5 = t1.t1col2
      AND t2.t2col6 = t1.t1col3
      AND t2.int_attribute_of_Tab1 > 0
)

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17640

A couple of definitions SqlServer https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql EXCEPT Returns any distinct values from the query to the left of the EXCEPT operator that are not also returned from the right query. PLsql https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm MINUS statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second

A pedantic translation to mysql would be

 SELECT distinct t1.* 
FROM Tab1 as t1
left outer join
(SELECT * 
FROM Tab1 
WHERE int_attribute_of_Tab1>0) as t2 on t1.id = t2.id
where t2.id is null;

Assuming there is an id column, And I wouldn't like to use distinct on a lot of columns.

Upvotes: 1

Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Try this 

SELECT * 
FROM Tab1
WHERE [....] NOT EXISTS 
(SELECT * 
FROM Tab1 
WHERE int_attribute_of_Tab1>0)

Upvotes: 5

Ben Swinburne
Ben Swinburne

Reputation: 26467

You could use NOT IN

SELECT * 
FROM Tab1
WHERE id  NOT IN (
    SELECT id 
    FROM Tab1 
    WHERE int_attribute_of_Tab1>0
)

Upvotes: 46

Related Questions