Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Querying Relational Databases Set Operations Except Operations

Eylon Cohen
Eylon Cohen
4,779 Points
Posted by 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
229,644 Points
Steven Parker
Steven Parker
229,644 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
Eylon Cohen
Eylon Cohen
4,779 Points

I thought MySQL is prefect :( Thanks!!

Steven Parker
Steven Parker
229,644 Points
Steven Parker
Steven Parker
229,644 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
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
229,644 Points
Steven Parker
Steven Parker
229,644 Points

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

Eylon Cohen
Eylon Cohen
4,779 Points
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