CODEWITHSUNDEEP
sql
user1324059
user1324059

Reputation: 89

SQL - Query Basic between 2 tables

I would like to know in MYSQL (editor HeidiSQL)

I have 2 Tables (email & spam) 2 rows (ID, EMAIL) and i want to clean my database : Tables Used in Set Operation Examples

                                EMAIL

                                  x  y
                           ------------------
                                  1  [email protected]     
                                  2  [email protected]     
                                  2  [email protected]      
                                  3  [email protected]    

                                SPAM

                                  x  z
                           ------------------
                                  1  [email protected]      
                                  2  [email protected]      
                                  4  [email protected]

USUALLY i use EXCEPT but it doesn't work on MYSQL.

proc sql; title 'EMAIL EXCEPT SPAM' select * from sql.EMAIL except select * from sql.SPAM;

Producing Rows That Are in Only the First Query Result (EXCEPT)

                               EMAIL EXCEPT SPAM

                                  x  y
                           ------------------
                                  3  [email protected]

i try to use WHERE NOT EXISTS but i don't know.

Can you help me for making the query :

SELECT * FROM EMAIL WHERE ... ????

Thank's

Upvotes: 1

Views: 95

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

USUALLY i use EXCEPT but it doesn't work on MYSQL.

EXCEPT is not supported in mySQL. Note that in absence of an explicit ALL | DISTINCT keyword, EXCEPT defaults to EXCEPT DISTINCT. Therefore, in your workaround you should explicitly use SELECT DISTINCT (because SELECT defaults to SELECT ALL).

i try to use WHERE NOT EXISTS

SELECT DISTINCT y 
  FROM EMAIL 
 WHERE NOT EXISTS ( SELECT * 
                      FROM SPAM
                     WHERE y = z );

Upvotes: 0

Nitin Midha
Nitin Midha

Reputation: 2268

Left Outer Join with Null in spam should work.

SELECT E.*
FROm EMAIL E
LEFT JOIN SPAM S
  ON E.Email = S.Email
WHERE S.Id IS NULL

Upvotes: 2

Mursa Catalin
Mursa Catalin

Reputation: 1449

SELECT * FROM EMAIL WHERE y NOT IN (SELECT z FROM SPAM)

Upvotes: 2

Related Questions