CODEWITHSUNDEEP
sqlpostgresqljoin
Bebbs
Bebbs

Reputation: 289

Select rows that don't have a corresponding join in join table

I have two SQL tables - customer and widget. There's a join table, customers_widgets between them, that has two columns (customer_id and widget_id)

Is there a way I can select all the customers that aren't joined to a widget? So they have an id that doesn't appear in the customer_id column on the join table?

Upvotes: 1

Views: 82

Answers (3)

MatBailie
MatBailie

Reputation: 86716

In general I've found NOT IN to be expensive and slow, but your mileage may vary on different RDBMS.

The two alternatives that I most often use are:

SELECT
    *
FROM
    customer
WHERE
    NOT EXISTS (SELECT *
                  FROM customers_widgets
                 WHERE customers_widgets.customer_id = customer.customer_id 
               )

And...

SELECT
    customer.*
FROM
    customer
LEFT JOIN
    customers_widgets
        ON  customers_widgets.customer_id = customer.customer_id
WHERE
    customer_widgets.customer_id IS NULL

Upvotes: 3

Siyual
Siyual

Reputation: 16917

You can use an OUTER JOIN for this:

Select    C.*
From      customer         C
Left Join customer_widgets W  On  C.customer_id = W.customer_id
Where     W.customer_id Is Null

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT customer_id
FROM customer
WHERE customer_id NOT IN (SELECT customer_id 
                          FROM customers_widgets)

Upvotes: 0

Related Questions