SQL in operator, SQL between operator, SQL alias (alias)

In operator

The in operator allows us to specify multiple values in the WHERE clause.

SQL in syntax

SELECT column_name (s) from Table_namewhere column_name in (value1,value2,...)

The original table (used in the instance:)

Persons table:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

In operator instance

Now, we want to select the person whose last name is Adams and Carter from the table above:

We can use the following SELECT statement:

SELECT * from Personswhere LastName in (' Adams ', ' Carter ')

Result set: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
3	Carter	Thomas	Changan Street	Beijing

The between operator is used in the WHERE clause to select a range of data between two values.

Between operator

operator between ... and selects a range of data between two values. These values can be numeric, text, or date.

SQL between syntax

SELECT column_name (s) from Table_namewhere Column_namebetween value1 and value2

The original table (used in the instance:)

Persons table:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing
4	Gates	Bill	Xuanwumen 10	Beijing

Between operator instance

To display people in alphabetical order between "Adams" (including) and "Carter" (not included), use the following SQL:

SELECT * from Personswhere LastName BETWEEN AND ' Carter '

Result set: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York

Important: Different databases on between ... The and operator is handled differently. Some databases list people between "Adams" and "Carter", but not "Adams" and "Carter," and some databases list people between "Adams" and "Carter" and include "Adams" and "Carter", while others The database lists people between "Adams" and "Carter", including "Adams", but excludes "Carter".

So, please check how your database handles between .... And operator!

Example 2

Use the NOT operator if you want to use the above example to show people outside the range:

SELECT * from Personswhere LastName NOT between ' Adams ' and ' Carter '

Result set: City

Id	LastName	FirstName	Address
3	Carter	Thomas	Changan Street	Beijing
4	Gates	Bill	Xuanwumen 10	Beijing

By using SQL, you can specify aliases (alias) for column names and table names.

SQL alias syntax for SQL alias table

SELECT column_name (s) from Table_nameas Alias_name

SQL Alias syntax for columns

SELECT column_name as Alias_namefrom table_name

Alias instance: Using Table name aliases

Let's say we have two tables: "Persons" and "Product_orders", respectively. We assign them the alias "P" and "Po", respectively.

Now we want to list all orders for "John Adams".

We can use the following SELECT statement:

AS pAS poWHERE p.lastname= ' Adams ' and p.firstname= ' John '

SELECT statements that do not use aliases:

SELECT Product_orders.orderid, Persons.lastname, Persons.firstnamefrom Persons, Product_orderswhere Persons.LastName = ' Adams ' and persons.firstname= ' John '

As you can see from the two SELECT statements above, aliases make the query program easier to read and write.

Alias instance: Use a Column name alias table Persons: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Sql:

AS FamilyAS NameFrom Persons

Results:

Family	Name
Adams	John
Bush	George
Carter	Thomas

SQL in operator, SQL between operator, SQL alias (alias)