Sql In

Sql In can improve your productivity.


The IN keyword is used as part of a condition, and when used with Sql Where, it enables us to specify multiple values, either as a list or a subquery. Subquery will be discussed separately, here we'll discuss a list of values.

 

 

 

 

The syntax:

SELECT  *
FROM  table_name
WHERE  column_name [NOT] IN (value1 [,value2, ...])

Where Arguments

column_name
Any column in the table. You can also use an expression.

NOT
Specifies that the result of the predicate be negated.

value1 [,value2,...]
A list of expressions to test for a match, they must be of the same type as column_name.

Example: Let's use the following Contacts table to illustrate the usage of Sql In. Select the names of people who live in Sydney,Surry Hill,Melbourne, or Coolangatta.

CPIdSurnameFirstnameAddressSuburbStatePostcode
1WhiteAlan210 George StSydneyNSW2000
2Johnson Benjamin 11 Cooper St Surry Hill NSW 2010
3Brown Peter 72 Haymarket St Haymarket NSW 2000
4Anderson Thomas 33 Collins St Melbourne VIC 3000
5Gomdas Tony 78 Marine Pde Coolangatta QLD 4225
6GreenTony 123 Second AveLiverpoolNULL2170
7GutmanPhilip27 Swanson StMelbourneVIC3000

SQL Statement:

SELECT Surname,Firstname,Address,Suburb
FROM Contacts
WHERE  Suburb IN ('Sydney','Surry Hill','Melbourne','Coolangatta')

Result:

SurnameFirstnameAddressSuburb
WhiteAlan210 George StSydney
Johnson Benjamin 11 Cooper St Surry Hill
Anderson Thomas 33 Collins St Melbourne
Gomdas Tony 78 Marine Pde Coolangatta
GutmanPhilip27 Swanson StMelbourne

Compare OR and IN
If you write the above Sql Statement using OR instead of IN operator, this is what you'll get.

SQL Statement:

SELECT surname,
       firstname,
       address,
       suburb
FROM   contacts
WHERE  suburb = 'Sydney'
        OR suburb = 'Surry Hill'
        OR suburb = 'Melbourne'
        OR suburb = 'Coolangatta' 

As you can see that with Sql In, you can write a simpler Sql.

You can also replace the list with an Sql statement, this is discussed further in the Sql subquery.

Quick link. From here, you can learn the following:

Sql Select
The core of Sql, selecting data from table.

Sql Where
Conditionally selecting rows from table.

Sql Distinct
Eliminating duplicate values in the output.

Sql Count
Counting number of rows.

Sql Sum
Getting the sum of a field

Sql Max
This function will return the highest value.

Sql Min
This function will return the lowest value.

Sql Avg
This function will return the average value.

Sql Order By
Sorting the output

Sql And Or
Connecting two or more conditions in the Where or Having clause.

Sql Group By
Grouping rows into sets.

Sql Having
To filter the result of aggregation

Sql Like
Pattern searching for conditions in Where or Having clause

Sql Between
Added to conditions to show results between two values.

Perseverance: Cliffhanger
Buy at AllPosters.com
What the mind can conceive and believe, it can achieve.

Return from Sql In to Sql Command

Positive Resources

All of us have at least two or three things that excite us. We could talk about these subjects for hours. Good news!. Did you know that Site Built It! can turn this type of deep interest and enthusiasm into a revenue-generating business?

Review some of the Site Build It! case studies and you'll absolutely discover something that ignites more positive ideas into your mind.

SBI! Video Tour


Invest the most
important 30 minutes
you will spend online.

Click, sit back, and
understand why SBI!
makes you succeed.