How to use NOT IN in an SQL subquery?

Question

I am trying to find the drinker and the drink liked by the drinker but the drinker hasn't ordered the drink so far. I also need to sort the drinker and the drink pairs in the ascending order of drinkers and drinks.

Note: I can only use IN or NOT IN operations. (I am also working in MYSQL)

I am very new to SQL so any advise would be greatly appreciated.

Here are the two tables I am trying to retrieve data from:

CREATE TABLE LIKES( /* Drinkers like drinks */
DRINKER     VARCHAR(30) NOT NULL,   /* Drinker name */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
RATING      DECIMAL(1)  NOT NULL,   /* Rating of the drink  */
    CONSTRAINT LIKES_PKEY PRIMARY KEY(DRINKER, DRINK),
    CONSTRAINT LIKES_FKEY1 FOREIGN KEY(DRINK) REFERENCES ALLDRINKS(DRINK),
    CONSTRAINT LIKES_DKEY2 FOREIGN KEY(DRINKER) REFERENCES DRINKERS(DRINKER));

CREATE TABLE ORDERS(    /* Drinkers visit pubs and consumes drinks */
DRINKER     VARCHAR(30) NOT NULL,   /* Drinker name */
PUB         VARCHAR(30) NOT NULL,   /* Pub name */
ODATE       DATE        NOT NULL,   /* Order date   */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
DRINK_NO    DECIMAL(2)  NOT NULL,   /* A sequence number of a drink */
    CONSTRAINT ORDERS_PKEY PRIMARY KEY(DRINKER, PUB, ODATE, DRINK, DRINK_NO),
    CONSTRAINT ORDERS_FKEY1 FOREIGN KEY(PUB, DRINK) REFERENCES SERVES(PUB, DRINK),
    CONSTRAINT ORDERS_FKEY2 FOREIGN KEY(DRINKER) REFERENCES DRINKERS(DRINKER)   );

Here is my SELECT statement:

SELECT DRINKER, DRINK
FROM LIKES
WHERE DRINKER NOT IN 
    (SELECT DRINKER
     FROM ORDERS 
     WHERE ODATE IS NULL)
     AND DRINK NOT IN
         (SELECT DRINK 
          FROM ORDERS 
          WHERE ODATE IS NULL)
          ORDER BY DRINKER AND DRINK ASC;

Barbaros Özhan · Answer 1 · 2020-05-16T04:21:19.600

2

You can briefly use NOT EXISTS with two columns combined in the subquery in addition to common condition ODATE IS NULL:

SELECT l.DRINKER, l.DRINK
  FROM LIKES l
 WHERE NOT EXISTS 
      ( SELECT 0
          FROM ORDERS 
         WHERE ODATE IS NULL
           AND ( DRINKER = l.DRINKER
              OR DRINK = l.DRINK ) )
 ORDER BY l.DRINKER , l.DRINK;

Demo

edited May 16 '20 at 04:21

answered May 16 '20 at 03:38

Barbaros Özhan

59,113
10
31
55

1

I was drafting the same answer with below consideration `ODATE is not null column` `Should be using exists` – Prahalad Gaggar May 16 '20 at 03:40
@Barbaros Özhan I can only use IN or NOT IN operations – Noods May 16 '20 at 03:42

Prahalad Gaggar · Answer 2 · 2020-05-16T10:57:05.953

1

SELECT l.DRINKER, l.DRINK
FROM LIKES l
WHERE CONCAT(l.DRINKER+'~~'+l.DRINK) NOT IN 
( 
   SELECT CONCAT(O.DRINKER+'~~'+O.DRINK)
   FROM ORDERS O
)
ORDER BY l.DRINKER , l.DRINK ASC;

edited May 16 '20 at 10:57

answered May 16 '20 at 03:45

Prahalad Gaggar

11,389
16
53
71

I have used this, however I am still receiving names of drinkers who have liked AND ordered drinks. I should only be receiving names of drinkers who have liked a drink but have not ordered it. – Noods May 16 '20 at 03:50
@Noods: I have modified the query as per request.. Please check – Prahalad Gaggar May 16 '20 at 10:57

How to use NOT IN in an SQL subquery?

2 Answers2