Databases Querying Relational Databases Set Operations Except Operations

Eylon Cohen
Eylon Cohen
4,779 Points

Using the same table on both sides of set operator

Hello!

I've been tring to use an EXCEPT operator when I use the same basic table in each SELECT, but failed to do so. for example:

SELECT * FROM table WHERE table.x = 10

EXCEPT

SELECT * FROM table WHERE table.y != 10

  • will not work. What am I missing here?....

Thank you!

4 Answers

Steven Parker
Steven Parker
201,933 Points

I didn't see anything wrong. So I did a quick search and found blogs talking about how "EXCEPT" is not a valid operator in MySQL. :open_mouth:

So you'll need to rework your query using "LEFT JOIN" or "NOT IN" with a sub-query.

Eylon Cohen
Eylon Cohen
4,779 Points

I thought MySQL is prefect :( Thanks!!

Steven Parker
Steven Parker
201,933 Points

How are you determining that it "will not work"? Are you getting an empty result set?

That's quite possible, depending on the data. Can you construct an example using the SQL playground?

I tried the standard one from the course and it worked.

Eylon Cohen
Eylon Cohen
4,779 Points

You are right, it does work on the playground. but I use MySQL 5.7 command line and it does not work there. This command is very basic... do you have any idea why it doesnt work there?

Steven Parker
Steven Parker
201,933 Points

I'd have to see the contents of "table".

Eylon Cohen
Eylon Cohen
4,779 Points

Simple one:

mysql> INSERT INTO table1 (id, value) VALUES (1,1), (2,2), (3,3), (4,1), (5,2), (6,1);

Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0

SELECT * FROM table1 WHERE table1.value<3 EXCEPT SELECT * FROM table1 WHERE table1.value>1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCEPT SELECT * FROM table1 WHERE table1.value>1' at line 2