SQL query with IN operator

Question

I'm very new to SQL and I'm trying to learn how to use IN operator.

There are 2 tables(customers,nation) but the nation name information is not included in the customers table. In customers table there is only the nation's ID. So my approach to get the nation name is the following :

SELECT C_ID,C_NAME,N_NAME
FROM customers,nation
WHERE N_NAME in(SELECT N_NAME FROM nation WHERE nation.N_NAME = "GERMANY")

The problem is that this query returns a table with all of the customers from all nations, and in the N_NAME column it says only "GERMANY" even there are lot of different nationalities.

The desired result is a table with those customers that are from Germany.

Bampfer · Accepted Answer · 2017-04-17 12:59:17Z

3

Your where clause is limiting the items from the Nation table, but not the Customers table. Or, to look at it another way, the subquery selects the right row but then you've added a new table to the query (Customers) with no limits.

The right way to solve this is not with the IN operator, but with a JOIN operator. Join the two tables together using the nation key in the customer table. You didn't give us all the column names so I will have to guess at the nation key.

SELECT c.C_ID, c.C_NAME, n.N_NAME FROM customers c
INNER JOIN nation n ON n.NATION_ID = c.NATION_ID;

That will give you the customers and their respective nation's name. To limit it to Germany, add a WHERE clause:

SELECT c.C_ID, c.C_NAME, n.N_NAME FROM customers c
INNER JOIN nation n ON n.NATION_ID = c.NATION_ID
WHERE n.NATION_NAME='Germany';

answered Apr 17, 2017 at 12:59

Bampfer

2,24017 silver badges26 bronze badges

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

Comments

Mansoor · Accepted Answer · 2017-04-17 13:04:34Z

1

Try below query

  SELECT C_ID,C_NAME,N_NAME
  FROM customers
  WHERE EXISTS
  (
   SELECT 1 FROM nation WHERE nation.N_NAME = "GERMANY" AND nation.Nationid 
   = customers.Nationid
  )

edited Apr 17, 2017 at 13:04

answered Apr 17, 2017 at 12:50

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

4 Comments

Fanarosss Over a year ago

Can you please explain to me what I did wrong, and the difference with the query that you gave me? What is the nation.N_NAME = customers.C_NAME comparison ? nation's name exists only in table nation

Mansoor Over a year ago

you not check customer table in anywhere.

Bampfer Over a year ago

C_NAME is probably customer name, not nation name. If it was the nation name you wouldn't need the Nations table at all.

Fanarosss Over a year ago

@Bampfer exactly

J. Michael Wuerth · Accepted Answer · 2017-04-17 12:58:56Z

0

Have you trie joining the customers and nation table? Try this query:

SELECT C_ID,C_NAME,N_NAME FROM customers INNER JOIN nation on customers.C_NAME = nation.N_NAME WHERE nation.N_NAME = 'GERMANY'

answered Apr 17, 2017 at 12:58

J. Michael Wuerth

3021 silver badge6 bronze badges

Comments

David Jacobsen · Accepted Answer · 2017-04-17 13:02:54Z

0

The issue is that you are cross joining customers and nation tables. Try

SELECT C_ID,C_NAME,N_NAME
FROM customers 
INNER JOIN nation
    on customers.NationID = nation.NationID
WHERE N_NAME in(SELECT N_NAME FROM nation WHERE nation.N_NAME = "GERMANY")

A cross join will join every record from one table to every record on the other. An INNER JOIN will only return records that exist in both tables based off of the on clause defining how the tables are joined.

answered Apr 17, 2017 at 13:02

David Jacobsen

4935 silver badges21 bronze badges

Collectives™ on Stack Overflow

SQL query with IN operator

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related