SQL query to find missing values in tables

Question

Table 1

Product    product Description
ABC        Hardware
CBD        Software
BCQ        Component
DEF        License

Table 2

Product  Product Description Order ID
ABC      Hardware            1234
ABC      Hardware            2345
ABC      Hardware            5678
BCQ      Component           7896
BCQ      Component           9681

Here is the background table 1 has the product data, table 2 has the product data with order ids I want to know how many products are not ordered (in this scenario "CBD Software" and "DEF License" has not orders placed hence is missing in table 2). now i want to know how many products were never placed on an order.

Request you to please accept the answer as it motivate me to answer more questions. — Suraj Kumar
– Suraj Kumar, Commented Jan 11, 2019 at 19:08

Suraj Kumar · Accepted Answer · 2019-01-11 12:01:14Z

3

You can try the below query

SELECT * FROM Table1
where ProductId not in (SELECT ProductId FROM ProductDescription)

Below is the actual example

Create table ProductDescription (ProdCd varchar(10), ProdDes Varchar(20))
insert into ProductDescription values ('ABC', 'Hardware'),
('CBD',     'Software'),
('BCQ',     'Component'),
('DEF',     'License')

Create table ProductOrders (ProdCd varchar(10), ProdDes Varchar(20), OrderId int)
insert into ProductOrders values ('ABC',      'Hardware',      1234),
('ABC',      'Hardware',      2345),
('ABC',      'Hardware',      5678),
('BCQ',      'Component',     7896),
('BCQ',      'Component',     9681)

SELECT * FROM ProductDescription
where ProdCd not in (SELECT ProdCd FROM ProductOrders)

The output is as shown below

ProdCd  ProdDes
---------------
CBD     Software
DEF     License

You can find the live demo here Live Demo

edited Jan 11, 2019 at 12:01

answered Jan 11, 2019 at 11:54

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shushil Bohara · Accepted Answer · 2019-01-11 12:07:14Z

2

You can also use LEFT JOIN and IS NULL in where condition

SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t2.product = t1.product
WHERE t2.product IS NULL

answered Jan 11, 2019 at 12:07

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2019-01-11 13:15:03Z

2

Do you just want not exists?

select p.*
from products p
where not exists (select 1 from orders o where o.product = p.product);

edited Jan 11, 2019 at 13:15

answered Jan 11, 2019 at 11:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

fdkgfosfskjdlsjdlkfsf Over a year ago

You forgot the orders alias o

fdkgfosfskjdlsjdlkfsf · Accepted Answer · 2019-01-11 13:11:13Z

1

Here's yet another way, with RIGHT JOIN and IS NULL:

declare @prod table
(
    Product varchar(100),
    [product Description] varchar(100)
)

insert into @prod
select 'ABC','Hardware' union
select 'CBD','Software' union
select 'BCQ','Component' union
select 'DEF','License'


declare @prodOrders table
(
    Product varchar(100),
    [product Description] varchar(100),
    [orderid] int
)
insert into @prodOrders
select 'ABC','Hardware',1234  union
select 'ABC','Hardware',2345  union
select 'ABC','Hardware',5678  union
select 'BCQ','Component',7896 union
select 'BCQ','Component',9681

SELECT t1.*
FROM @prodOrders t2 
right JOIN @prod t1 ON t2.product = t1.product
WHERE t2.product IS NULL

Live Demo

answered Jan 11, 2019 at 13:11

fdkgfosfskjdlsjdlkfsf

3,3464 gold badges51 silver badges122 bronze badges

Collectives™ on Stack Overflow

SQL query to find missing values in tables

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related