MySQL Select rows that are in one table but not in another

I've had to do this a couple times, and every time, I look around on Google for some good solutions, but don't find much. Basically, I have two tables of data, and I want to see if there are any rows in the first table that aren't in the second (or, conversely, I only want values that are in the first table AND the second).

To select rows in the first table that don't have any corresponding values in the second, try:

<br />
SELECT first.*<br />
FROM first_table first<br />
LEFT JOIN second_table second ON first.id = second.id<br />
WHERE second.id IS NULL<br />

Conversely, if you just want to select rows in the first table that are also in the second (but discard rows that don't have corresponding values in the second), try:

<br />
SELECT first.*<br />
FROM first_table first<br />
LEFT JOIN second_table second ON first.id = second.id<br />
WHERE second.id IS NOT NULL<br />

For my Drupal site, I needed to do something like:

<?php
 
<br /
?>
$result = db_query("SELECT subs.*
FROM {custom_subscriptions} subs
LEFT JOIN {flag_content} flag ON flag.content_id = subs.content_id AND flag.uid = subs.uid
WHERE subs.content_id = :content_id
AND flag.content_id IS NOT NULL", array(
':content_id' => $content_id,
))->fetchAllAssoc('uid');
?>

Hope this helps!

Comments