2

I have five results to retrieve from a table and I want to write a store procedure that will return all desired rows.

I can write the query like that temporarily: 

Select * from Table where Id = 1 OR Id = 2 or Id = 3

I supposed I need to receive a list of Ids to split, but how do I write the WHERE clause?

George Stocker
  • 57,289
  • 29
  • 176
  • 237
Karine
  • 385
  • 1
  • 7
  • 18

2 Answers2

6

So, if you're just trying to learn SQL, this is a short and good example to get to know the IN operator. The following query has the same result as your attempt.

SELECT * 
FROM TABLE 
WHERE ID IN (SELECT ID FROM TALBE2)

This translates into what is your attempt. And judging by your attempt, this might be the simplest version for you to understand. Although, in the future I would recommend using a JOIN.

A JOIN has the same functionality as the previous code, but will be a better alternative. If you are curious to read more about JOINs, here are a few links from the most important sources

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
4

Another way to do it. The inner join will only include rows from T1 that match up with a row from T2 via the Id field. 

select T1.* from T1 inner join T2 on T1.Id = T2.Id

In practice, inner joins are usually preferable to subqueries for performance reasons.

Community
  • 1
  • 1
15ee8f99-57ff-4f92-890c-b56153
  • 36,786
  • 7
  • 62
  • 114