SQL: the power of left, right and IN

Tweet

As the title mentions, in this short article I will show you how to benefit from the use of the left and right SQL functions together with the IN operator.
Before doing so, we need to understand what are the two functions and what the operator is doing.
Please follow me and see what we can do!

The left and right functions
The two functions work perfectly when we need to isolate part of a string. They return the left or right part of a character string with the specified number of characters.
As an example, the syntax for the left function is:

LEFT(strVar, numChr)

Where strVar is a character string and numChr is a positive number that specifies how many characters we want to get from the string (strVar).
Consider that the same is working for the right function.
The difference between the two functions is that left starts considering the string from the left hand side, while right considers the characters from the right hand side of the string.
As a general example we can create a query like:

SELECT LEFT(name, 2)
FROM namesTable

If our names (name) in the table (namesTable) are “John”, “Jack” and “Daniel” the results from the above query will be: “Jo”, “Ja” and “Da”. We are getting just the leftmost two characters from the name column.
To simplify the above example we can create two simple queries for the left and right functions:

SELECT LEFT(‘abcdefg’, 2)
SELECT RIGHT(‘abcdefg’, 2)

The first query will return ‘ab’ while the second will return ‘fg’.

The IN operator
The IN operator can be used to determine if a specified value matches any value in a subquery or a list.
Now... now... that might sound complicated, but it’s not.
A common use of the IN operator is in the WHERE clause. We can in fact convert a query like:

SELECT *
FROM namesTable
WHERE name = ‘John’ or name = ‘Jack’ or name = ‘Daniel’

To a query like:

SELECT *
FROM namesTable
WHERE name IN (‘John’, ‘Jack’, ‘Daniel’)

Isn’t it cool?
There something more interesting. We can even use another operator... well, actually the opposite operator: NOT IN. As you imagine NOT IN will return the names different from ‘John’, ‘Jack’ and ‘Daniel’:

SELECT *
FROM namesTable
WHERE name NOT IN (‘John’, ‘Jack’, ‘Daniel’)

All together now!
Now, we can combine the two functions and the IN operator. How?
Let’s say we want to retrieve all records only in specific cases. In T-Sql we can use a conditional statement and execute a query based on that.

IF (SELECT COUNT(*) FROM namesTable) > 1
BEGIN
... ... ...
END

The above executes the code inside the BEGIN END only if the table namesTable contains more than 1 record.
At the same time we can use something like:

SELECT *
FROM namesTable
WHERE LEFT(name, 1) IN (‘J’, ‘D’)

Which returns all records that have a name starting with J or D.
Now we can put all together:

IF EXISTS (SELECT * FROM namesTable WHERE LEFT(name, 1) IN (‘J’, ‘D’))
BEGIN
SELECT * FROM otherTable
END

The above example is just to explain the logic: if at least a name in the namesTable starts with ‘J’ or ‘D’ the second statement is executed, retrieving all the data from anothe table (otherTable).

This is just the beginning, as you may imagine. I leave to your fantasy how to use the above, however, if you want to share your experience just use the comment section below.
In the meantime... happy coding!

(Thanks to Paolo for the hint!)