Question

[Solved] 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?

Solution #1:

You could use NOT IN

SELECT * 
FROM Tab1
WHERE id  NOT IN (
    SELECT id 
    FROM Tab1 
    WHERE int_attribute_of_Tab1>0
)
Respondent: Ben Swinburne

Solution #2:

Try this

SELECT * 
FROM Tab1
WHERE [....] NOT EXISTS 
(SELECT * 
FROM Tab1 
WHERE int_attribute_of_Tab1>0) 
Respondent: Ryan Gadsdon

Solution #3:

A couple of definitions
SqlServer https://docs.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.

Respondent: P.Salmon

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Most Popular

To Top
India and Pakistan’s steroid-soaked rhetoric over Kashmir will come back to haunt them both clenbuterol australia bossier man pleads guilty for leadership role in anabolic steriod distribution conspiracy