SQL filter query on FIFA player data

In SQL you can set up criteria to query only the specific rows that interest you the most. The WHERE clause is used in SQL filter queries to apply the conditions in SELECT, INSERT, UPDATE, or DELETE sentences.

For the scope of this post, we’ll describe how to use comparison, special, and Boolean operators with the WHERE clause . You’ll also learn how to build subqueries and a CASE WHEN expression using WHEN.

We’ll show the examples using the FIFA players 2018 dataset that contains all the statistics and playing attributes of all the players in the full version of FIFA 18. Note: This article covers basic SQL filter queries and is dedicated to SQL beginners. For the sake of simplicity, we took only the first 200 rows from the dataset. The example queries work well with Amazon Redshift, but some of them may conflict with other SQL databases.

Comparison Operators

In the WHERE clause, you can use the following operators, which are called relational, since they compare the values of two operands:

= Equal. A column or expression matches a value. <> Not equal. Use when you need to exclude a value. > Greater than < Less than >= Greater than or equal <= Less than or equal

Note: • In some versions of SQL the inequality operator may be written as !=. • All uppercase characters are less than all lowercase characters, so ‘Z’ < ’a’, but ‘a’ < ‘z’.  Also, all numbers are less than all characters, so ‘1’ < ‘Z’.

SELECT name, age FROM fifa_dataset WHERE club = ‘Real Madrid CF’;

Powered byStatsbot

Note: Text fields in SQL require ‘single quotes’, while numeric fields don’t.

Boolean expressions

These expressions test for the truth of some condition. To filter query results in SQL, you can use AND, OR, and NOT .

AND. TRUE if all of a set of comparisons are TRUE.

OR. TRUE if either of a set of comparisons is TRUE.

NOT. Reverses the value of any other Boolean operator.

SELECT name FROM fifa_dataset WHERE club = ‘Real Madrid CF’ AND strength>=80>;

Powered byStatsbot

Boolean expressions seem to be simple unless you combine them in a complex SQL filter query. You can effectively manage Boolean and relational expressions using parentheses, which determine the order of operations. When you see a long WHERE clause with Boolean expressions, to keep things simple you need to estimate the value of internal operators and then apply the upper external operator.   Keep doing this until the expressions end. :wink:

SELECT name, club FROM fifa_dataset WHERE NOT((age >= 30 AND balance < 70) OR potential < 90);

Powered byStatsbot

Special operators

IN() operator

Instead of writing a complex WHERE clause using Boolean expressions, you can put your values in a list. The IN() operator checks values of a given column by trying to find a match with the values that are inside of a list.

SELECT name, club FROM fifa_dataset WHERE club in (‘Chelsea’, ‘Liverpool’, ‘FC Barcelona’);

Powered byStatsbot

BETWEEN operator

BETWEEN operator is similar to IN(). Between defines the range of values that should go in decreasing order. A query will return the value in a given range.

SELECT name FROM fifa_dataset WHERE potential BETWEEN 90 AND 95;

Powered byStatsbot

Note: Unlike from IN, BETWEEN is sensitive to the order, and the first value in the operator must be the first in alphabetical or numerical order.

LIKE operator

If you are looking for a name or another value and do not remember how exactly they are written, the LIKE (or NOT LIKE) operator is what you need. It looks for values that match a given pattern. As a condition, LIKE uses group symbols (wildcards). There are two types of wildcards:

• % replaces any number of symbols before, inside, or after a search term.  
• _ replaces only one symbol.

SELECT name FROM fifa_dataset WHERE club LIKE ‘%senal’ and club NOT LIKE ‘Juv_ntus’;

Powered byStatsbot

Subqueries with the WHEN clause

You can also use aggregate functions in a WHEN clause along with Boolean expressions, but this works only in subqueries.

SELECT name, age, club FROM fifa_dataset WHERE age =(SELECT min(age) FROM fifa_dataset);

Powered byStatsbot

Note: You don’t need to use subqueries if you use HAVING instead of WHERE for aggregate functions.

Case When with WHEN clause

A more complex SQL filter query (but not always the optimal one) can use a CASE expression in the WHERE clause. The CASE function itself allows you to return a value when it matches your conditions.

SELECT name, potential, club
 
FROM fifa_dataset
 
WHERE
 
potential = CASE
 
WHEN potential < 90 THEN potential
 
END;