NOT, AND & BETWEEN Commands in SQL

Leave a reply

This post wuill provide an explanation of how to use the following commands in SQL

  • NOT
  • AND
  • BETWEEN

NOT

The NOT command is used to exclude data based on some criteria. In the example, below we will use the NOT command to exclude basketball players who play the power forward or shooting guard position. The code is next followed by the output.

SELECT *
FROM Seasons_Stats
WHERE Pos NOT IN ('PF','SG')

Here is a breakdown of the code.

  1. Line 1 tells SQL to select all columns in the dataset
  2. Line 2 explain which table to use
  3. Line 3 Use the WHERE command first which serves as a filter. Next, the NOT command means to exclude what comes next. The IN command indicates specifically what will be excluded. The information in the parentheses is the categories to remove from the column called “POS”

IF you look at the first ten rows shown above you will see there are no PF or SG in the POS column which is what we wanted.

The example above excluded text data but we can also exclude numerical values as well. In the code below we replace the “Pos” column with the “Year” and we exclude the Years of 1950 and 1051. The code and output are below.

SELECT *
FROM Seasons_Stats
WHERE "Year" NOT IN (1950,1951)

If you look at the “Year” colum you can see it does not start until 1952. In addition. you can clearly see that there are PF and SG in the “Pos” column which used to be what we exlcuded in the previous example.

AND Command

The AND command is used whe n you have multiple criteria for including or excluding information. In the code below we continue to exlcude plauers from 1950 and 1951 but then with the AND command we exclude players whose first name starts with an A. Below is the code and output.

SELECT *
FROM Seasons_Stats
WHERE "Year" NOT IN (1950,1951) AND NOT Player LIKE 'A%'
ORDER BY Player

What’s new this time is the use of the LIKE command which is used when you are not exactly sure what you are looking for. THis is way the “A” is in sinle quotes followed by the percent sign. THis tells SQL to exclude anything that starts with an A.

BETWEEN Command

The BETWEEN command is when you are searching to include values that fit a certain range. In the code below, we remove the NOT command and include the BETWEEN and the AND command. THis means that we want to include rows that meet our critria rather than exlcude them.

SELECT *
FROM Seasons_Stats
WHERE Age BETWEEN 41 AND 43
ORDER BY Age

In this code we want any player who is between 41 and 43 years of age. Below is the same output but written with the use of the NOT command

SELECT *
FROM Seasons_Stats
WHERE Age NOT BETWEEN 18 AND 40
ORDER BY Age

Which is better probably comes down to preference

Conclusion

The commands mentioned here are among some of the basic tools anybody who wants to use SQL needs to know. In the future we will deal with other concepts related to SQL

Leave a Reply