Today, learning to use a subquery to filter, according to the book in the way to switch to the exists, the result is not the same as

- use the lookup in order items (prod_id) for RGAN01 customer information
The SELECT CUST_NAME, CUST_CONTACT
The FROM CUSTOMERS
WHERE CUST_ID IN
(SELECT CUST_ID
FROM the ORDERS
WHERE ORDER_NUM IN
(SELECT ORDER_NUM FROM ORDERITEMS WHERE PROD_ID='RGAN01'));

The following for the query result:



- use the exists to find order items for RGAN01d customer information
The SELECT CUST_NAME, CUST_CONTACT
The FROM CUSTOMERS
WHERE the EXISTS
(SELECT CUST_ID
FROM the ORDERS
WHERE the EXISTS
(SELECT ORDER_NUM FROM ORDERITEMS WHERE PROD_ID='RGAN01'));

The following for the query result:


Ask why two different results? Use and what is the difference exists in?

CodePudding user response:

Combined with the ORDERS table and the ORDERITEMS table field equal conditions

CodePudding user response:

The SELECT CUST_NAME, CUST_CONTACT
The FROM CUSTOMERS a
WHERE the EXISTS
(SELECT *
FROM the ORDERS b
WHERE a.C UST_ID=Arthur c. UST_ID and EXISTS
(SELECT * FROM the ORDERITEMS c WHERE PROD_ID='RGAN01' and b.O RDER_NUM=c.O RDER_NUM)
);

Such execution, the result should be the same

CodePudding user response:

Well, thank you, forget to add conditions, and by the way, you write the first condition in the where clause should be a.C UST_ID=biggest UST_ID

CodePudding user response:

The first thing you need to know what is the difference between in and the exists, an in can be return value, while the exists just tell you it's in