Eylon Cohen
4,779 PointsUsing 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
205,178 PointsI 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.
So you'll need to rework your query using "LEFT JOIN" or "NOT IN" with a sub-query.
Steven Parker
205,178 PointsHow 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
4,779 PointsYou 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
205,178 PointsI'd have to see the contents of "table".
Eylon Cohen
4,779 PointsSimple 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
Eylon Cohen
4,779 PointsEylon Cohen
4,779 PointsI thought MySQL is prefect :( Thanks!!