CODEWITHSUNDEEP
mysql
Ninet9
Ninet9

Reputation: 183

Is there a faster and better way to do this mysql query?

Lets assume i have two tables. Item, and Storage.
There is a relation between them by item id.
What i want is to list every item, that has no relation found in Storage
I have a hunch that my statement could be a lot simpler than this: 

SELECT item.name,storage.stuff 
FROM item 
LEFT JOIN storage ON storage.item_id=item.id 
WHERE storage.stuff IS NULL 
GROUP BY item.id

Is there a way to replace LEFT JOIN to a different one so i return the item where nothing to join ?
(i actually want to keep the returning of the stuff field even if it is always NULL, because i have a query function that is serving clientside over ajax with a lot of ways to list the items, and i dont want to check there whether that field is exists or not, neither in the fetching part)

Table Item
id     name
1       a
2       b
3       c
4       d

Table Storage
item_id      stuff
1             this
1             that
2             this

Desired result: 

item_id     item_name     stuff
   3           c           NULL
   4           d           NULL

Upvotes: 0

Views: 38

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

There are more methodes for this..

LEFT JOIN method

Query

SELECT
    Item.id
  , Item.name
  , Storage.stuff 
FROM
  Item 
LEFT JOIN
  Storage
ON
  Storage.item_id = Item.id 
WHERE
  Storage.stuff IS NULL

NOT EXISTS method

Query

SELECT
   Item.id
 , Item.name
 , NULL AS stuff
FROM 
 Item
WHERE
 NOT EXISTS (
  SELECT 
   1
  FROM 
   Storage
  WHERE 
   Storage.item_id = Item.id
)

NOT IN method

Query

SELECT
   Item.id
 , Item.name
 , NULL AS stuff
FROM 
 Item
WHERE
 Item.id NOT IN (
    SELECT 
     Storage.item_id
    FROM 
     Storage
    WHERE 
      Storage.item_id = Item.id     
 )

see demo https://www.db-fiddle.com/f/phKyPgvGJpZ5x1Lj6sisCd/0

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You don't need to use Group By in the query, as you are fetching the cases where there is no match in the storage table. Your Group BY is not valid ANSI SQL either, as non-aggregated columns / non-group-by columns are being fetched in the Select clause.

SELECT item.id, item.name, storage.stuff 
FROM item 
LEFT JOIN storage ON storage.item_id=item.id 
WHERE storage.stuff IS NULL

Upvotes: 2

Related Questions