CODEWITHSUNDEEP
sqlsql-server
Zelkop
Zelkop

Reputation: 109

Inner join filtering out desired results

I'm just wondering how to fix this issue:

For example, when I type in this simple query

SELECT CUSTOMERS.CUSTOMER_ID, CUSTOMERS.NAME
FROM CUSTOMERS
WHERE (CUSTOMERS.NAME LIKE 'O%' AND CUSTOMERS.NAME LIKE '%e%') OR CUSTOMERS.NAME LIKE '%t';

I get the following output:

127 Alphabet
128 Comcast
129 Target
196 DuPont
197 Avnet
44  Jabil Circuit
58  Health Net
69  Whole Foods Market
226 Office Depot
260 Occidental Petroleum
27  Assurant
158 Owens & Minor
174 Oracle
255 Waste Management
88  Walmart
113 Microsoft
117 Home Depot

However, when I add INNER JOIN ORDERS ON CUSTOMERS.CUSTOMER_ID = ORDERS.CUSTOMER_ID I get this output instead.

44  Jabil Circuit
44  Jabil Circuit
44  Jabil Circuit
58  Health Net
69  Whole Foods Market
44  Jabil Circuit
44  Jabil Circuit

It seems like it's only displaying ID's and Names for customers that have an ID in the ORDERS table. How do I make it so it runs through every customer again, not just the ones in the ORDERS table?

Upvotes: 0

Views: 46

Answers (1)

andrbrue
andrbrue

Reputation: 731

You should use a LEFT JOIN instead of the INNER JOIN. A LEFT JOIN tries to match an entry of the second table to one of the first table while also displaying results for the first table where no matching entry in the second table exists. In this case the entries for the second table will just be NULL. An INNER JOIN only gives you data where entries for both sides are given.

Other possibilities are RIGHT JOIN which is like LEFT JOIN but switches the roles of the first and second table and FULL OUTER JOIN which only requires one side to be given.

Further information and some good examples can be found at w3schools.

Upvotes: 1

Related Questions