This post will explore the use and application of the WHERE, LIKE, and IN commands in SQL.
The WHERE command is generally used as a way to filter data in SQL. The database we are using in this post contains data on basketball players from 1950-2017. What we want to do is filter the data so that we only see data from players who played in 2017. In the example below, we will filter our basketball players by year be set to 2017
SELECT * FROM Seasons_Stats WHERE year = 2017
If you look at the year column you can clearly see that the value is set to 2017 just as we wanted it.
The WHERE command is not limited to numbers as it can also be used with text. In the example below, we filter our data with the WHERE command so that we only see players who played for the Golden State Warriors (GSW).
SELECT * FROM Seasons_Stats WHERE Tm = 'GSW'
If you look closely at the “Tm” column you will only see the initials for the Golden State Warriors.
The WHERE command is often teamed with the LIKE command when you are looking for a text but are not sure of an exact match. You can specify a pattern you are looking for similar to regular expressions. In the code below we use the LIKE command with the WHERE command searching for any player whose name begins with L.
SELECT * FROM Seasons_Stats WHERE Player LIKE 'L%'
The percentage sign (%) after the letter L tells SQL that anything can be after the letter L in the search and meet the criteria. We can also put the % at the end of or text or put one on both sides are some other combination as shown in the examples below.
In the example below, the last letter must be an L. After this example, is one in which an L must appear anywhere in the name.
SELECT * FROM Seasons_Stats WHERE Player LIKE '%L'
SELECT * FROM Seasons_Stats WHERE Player LIKE '%L%'
The IN command allows you to filter your data based on several values. This command is also combined with the WHERE command. In the example below, we filter our data so that we only see players whose position is small forward or shooting guard.
SELECT * FROM Seasons_Stats WHERE Pos in ('SG', 'SF')
You could also insert numerical values in the parentheses when using the IN command.
The command shared in this post provide more information on basic tools you can use and apply in SQL. Filtering using the WHERE, LIKE, and IN commands get helpo you to focus the breath of your research to find answers to your questions.