kemotoe kemotoe - 2 months ago 5
SQL Question

More efficient way to write this SQL query

I have three relations (simplified for the problem)

SUPPLIER(SNO)
PART(PNO, PNAME)
SHIPMENT(SNO, PNO)


where
SNO
and
PNO
are foreign keys matching the primary keys of
SUPPLIER
and
PART


I wrote a query to find
SNO
of all Suppliers that ship to P2 and P4. The query works but it seems inefficient. I excluded all
PNO
that were not P2 or P4

SELECT SUPPLIER.SNO, PNO
FROM SUPPLIER
JOIN SHIPMENT ON SUPPLIER.SNO = SHIPMENT.SNO
WHERE PNO <> 'P1' AND PNO <> 'P3' AND PNO <> 'P5' AND PNO <> 'P6'


There has to be as better way to write this query. I am using mssql-server

EDIT: I am just learning sql and was not aware of the IN operator. Thanks for the help

Answer

You can try the IN clause

SELECT SUPPLIER.SNO, PNO
FROM SUPPLIER
JOIN SHIPMENT ON SUPPLIER.SNO = SHIPMENT.SNO
WHERE PNO IN ('P2','P4')
Comments