I have 3 tables
PRODUCT
ID -------COUNTRY
1 --------Singapore
2 --------VietNam
3 --------Singapor
4 --------Campuchia
RECEIPT
IDPRO -------IDBILL
1 -------A
2 -------B
3 -------A
3 -------C
4 -------B
BILL
CODE
A
B
C
The question my teacher ask:list all CODE bought all products from Singapore
This is his answer:
SELECT CODE
FROM BILL B
WHERE NOT EXISTS (
SELECT * FROM PRODUCT P
WHERE COUNTRY='Singagpore' and not exists(
SELECT * FROM RECEIPT R
WHERE B.CODE=R.IDBILL AND P.ID=R.IDPRO))
It works prefectly The result is: A
The problem is that when I run the command line of the subquery, there are no results
SELECT * FROM PRODUCT P
WHERE COUNTRY='Singagpore' and not exists(
SELECT * FROM RECEIPT R
WHERE P.ID=R.IDPRO)
Suppose the above result is assigned K.
the rest of the command line will be:
SELECT CODE
FROM BILL B
WHERE NOT EXISTS (K)
How can it get any result like my teacher did while it uses empty results as reference?
I have searched for how to use NOT EXISTS, but in division it kinda hard
CREATE TABLE [dbo].[bill] (
[code] VARCHAR(100) NOT NULL
);
CREATE TABLE [dbo].[product] (
[product_id] INT NOT NULL,
[country] VARCHAR(100) NOT NULL
);
CREATE TABLE [dbo].[receipt] (
[receipt_id] INT NOT NULL IDENTITY (1, 1),
[product_id] INT NOT NULL,
[bill_id] VARCHAR(100) NOT NULL
);
INSERT INTO bill (code) values ('A')
INSERT INTO bill (code) values ('B')
INSERT INTO bill (code) values ('C')
INSERT INTO product (product_id, country) values (1,'Singapore')
INSERT INTO product (product_id, country) values (2,'VietNam')
INSERT INTO product (product_id, country) values (3,'Singapore')
INSERT INTO product (product_id, country) values (4,'Campuchia')
INSERT INTO receipt (product_id, bill_id) values (1,'A')
INSERT INTO receipt (product_id, bill_id) values (2,'B')
INSERT INTO receipt (product_id, bill_id) values (3,'A')
INSERT INTO receipt (product_id, bill_id) values (3,'C')
INSERT INTO receipt (product_id, bill_id) values (4,'B')
Returns A:
SELECT code
FROM BILL B
WHERE NOT EXISTS (
SELECT * FROM PRODUCT P
WHERE COUNTRY='Singapore' and not exists(
SELECT * FROM RECEIPT R
WHERE B.CODE=R.bill_id AND P.product_id=R.product_id))
This is because the code in the NOT EXISTS clause will run for every row in BILL and if the select returns nothing the row will be selected. If the select returns something then it will not be selected. The reverse will happen if you use EXISTS instead of NOT EXISTS. This returns nothing for A, so A will be returned:
SELECT * FROM PRODUCT P
WHERE COUNTRY='Singapore' and not exists(
SELECT * FROM RECEIPT R
WHERE 'A'=R.bill_id AND P.product_id=R.product_id)
Returns something for B, so B will not be returned:
SELECT * FROM PRODUCT P
WHERE COUNTRY='Singapore' and not exists(
SELECT * FROM RECEIPT R
WHERE 'B'=R.bill_id AND P.product_id=R.product_id)
Returns something for C, so C will not be returned:
SELECT * FROM PRODUCT P
WHERE COUNTRY='Singapore' and not exists(
SELECT * FROM RECEIPT R
WHERE 'C'=R.bill_id AND P.product_id=R.product_id)