SELECT * Y, where NOT in Table X

Databases
1 
	$sql = "SELECT * FROM `posts` WHERE 
	`posts`.`post_id` NOT EXISTS (SELECT `post_id` FROM `scene`)";
	
	$sql = "SELECT post_id FROM `posts` WHERE NOT EXISTS (SELECT `post_id` FROM `scene` WHERE 1)";

The first query does not work, this is what I need to work to deliver the results.

The second query works, but returns post_id’s and not * as I wanted in the first query.
I could work from these querying every post id, to get the data required, but would involve a lot of more in database.

Sorry for short post, but running out. Any help appreciated.

2

Makes a lot of sense. Thank you :slight_smile:

3

in order to verify that something is NOT IN a list, you pretty much have to compare every item in the list, right?

this is not a big deal if the list is a collection of hardcoded literal values (constants), but if the list is a subquery, it means all the rows that the subquery returns need to be checked individually for each row of the outer query

however, a NOT EXISTS will typically need to do only one retrieval (because it’s typically a correlated subquery), so as soon as it retrieves a single row, it can stop retrieving

make sense?

4

um, no

also, your NOT IN solution was already given (see post #2)

5

The problem may be arise due to the use of Not exist
you can use Not IN
Key word like following

SELECT *FROM posts WHERE posts.post_id NOT IN
(
SELECT
post_id
FROM
scene
)

6

Interesting. Could you explain why that is? And are there cases where you would prefer NOT IN over NOT EXISTS, even trough it’s less efficient?

7

Both examples worked a treat, thank you both for your time…

The dreaded evil select star wont be used, just in very early stages so indecisive of what data to show on this page, or leave out until post page.

Thanks.

8

the NOT EXISTS correlated subquery is often more efficient than the corresponding NOT IN subquery, although you have to write it correctly…

SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star 
  FROM posts
 WHERE NOT EXISTS 
       ( SELECT 1
           FROM scene
          WHERE post_id = posts.post_id )
9

You need to use NOT IN in the first query, instead of NOT EXISTS:

SELECT
  *
FROM
  `posts`
WHERE 
  `posts`.`post_id`
    NOT IN
    (
      SELECT 
       `post_id`
      FROM
       `scene`
    )

:slight_smile:

PS. Please avoid using the dreaded SELECT * but specify the fields you need instead :slight_smile: