CODEWITHSUNDEEP
sql-servert-sql
Sewder
Sewder

Reputation: 754

SQL best alternative to a LEFT JOIN and WHERE statement?

Basically I have two tables. Customer and Purchase table. My Problem is the Purchase Table is very large and causing performance issues, and I'm trying to keep my code organized into relevant CTE's.

I'm trying to pull all the purchase records for those who purchased a Guitar Type A or have no purchases.

I want to filter out any customer who didn't buy a GuitarType A but still keep customer who didn't buy anything.

Here's my code:

WITH Purchases AS 
(
    SELECT
        , CustID
        , GuitarType
    FROM
        Purchase
    WHERE
        GuitarType = 'A'
)
,
RelevantCustomers AS
(
    SELECT
        P.Custid
        , P.PurchaseDate
        , C.CustType
    FROM
        Customer

)
SELECT
    Custid
    , GuitarType
    , PurchaseDate
FROM
    Purchases AS p
INNER JOIN
    RelevantCustomers AS rc ON p.CustId= rc.CustId

Customer:

  +--------+-------------+----------+
    | CustId | CreatedDate | CustType |
    +--------+-------------+----------+
    | 1      | 01/01/2017  | A        |
    +--------+-------------+----------+
    | 2      | 01/01/2018  | B        |
    +--------+-------------+----------+
    | 4      | 01/01/2018  | C        |
    +--------+-------------+----------+

Purchase

+----------+--------------+------------+
| GuitarId | PurchaseDate | GuitarType |
+----------+--------------+------------+
| 1        | 04/01/2018   | A          |
+----------+--------------+------------+
| 1        | 05/01/2018   | A          |
+----------+--------------+------------+
| 1        | 06/01/2018   | C          |
+----------+--------------+------------+
| 2        | 06/01/2018   | A          |
+----------+--------------+------------+
| 2        | 06/01/2018   | B          |
+----------+--------------+------------+
| 2        | 06/01/2018   | A          |
+----------+--------------+------------+

If I use INNER JOIN then it will only return those who bought Guitar Type A. If I use LEFT then it will include all Customers.

One alternative is to move the "Where GuitarType = 'A' down to the where clause and do a LEFT JOIN but this will cause my code to be unorganized and potentially some performance issues.

Upvotes: 0

Views: 162

Answers (2)

paparazzo
paparazzo

Reputation: 45096

This might do it

SELECT rc.Custid, p.GuitarType, p.PurchaseDate
  FROM RelevantCustomers rc 
  LEFT JOIN Purchases p 
    ON p.CustId = rc.CustId
  LEFT JOIN Purchases pn 
    ON pn.CustId = rc.CustId 
   AND p.GuitarType != 'A'
 WHERE (p.GuitarType = 'A' OR p.CustID IS NULL)  
   and  pn.CustID is null

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You appear to want:

SELECT rc.Custid, p.GuitarType, p.PurchaseDate
FROM RelevantCustomers rc LEFT JOIN
     Purchases p 
    ON p.CustId = rc.CustId
WHERE p.GuitarType = 'A' OR p.GuitarType IS NULL;

For performance, you want an index on Purchases(CustId).

Upvotes: 1

Related Questions