SQL Operators

#sql #databases

SQL Operators

Sometimes, expressions with a single condition are not enough to satisfy the user's requirement. For that, SQL supports Logical Operators to use multiple conditions at once. The most common logical operators are AND, OR, and NOT.

AND Operator

The AND operator takes in two conditions and returns true or false based on their evaluation:

condition1 AND condition2

The result of the AND operation is true if and only if both condition1 and condition2 evaluate to true:

mysql> SELECT 1 = 1 AND 'test' = 'test';

+---------------------------+
| 1 = 1 AND 'test' = 'test' |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT 1 = 1 AND 'test' = 'abc';

+--------------------------+
| 1 = 1 AND 'test' = 'abc' |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

In MySQL terms, any non-zero value is considered true, and it usually returns the value 1 to signify true. 0 is considered false. As we can see in the example above, the first query returned true as both expressions were evaluated as true. However, the second query returned false as the second condition 'test' = 'abc' is false.

OR Operator

The OR operator takes in two expressions as well, and returns true when at least one of them evaluates to true:

mysql> SELECT 1 = 1 OR 'test' = 'abc';

+-------------------------+
| 1 = 1 OR 'test' = 'abc' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT 1 = 2 OR 'test' = 'abc';

+-------------------------+
| 1 = 2 OR 'test' = 'abc' |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

The queries above demonstrate how the OR operator works. The first query evaluated to true as the condition 1 = 1 is true. The second query has two false conditions, resulting in false output.

NOT Operator

The NOT operator simply toggles a boolean value 'i.e. true is converted to false and vice versa':

mysql> SELECT NOT 1 = 1;

+-----------+
| NOT 1 = 1 |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT NOT 1 = 2;

+-----------+
| NOT 1 = 2 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

As seen in the examples above, the first query resulted in false because it is the inverse of the evaluation of 1 = 1, which is true, so its inverse is false. On the other hand, the second query returned true, as the inverse of 1 = 2 'which is false' is true.

Symbol Operators

The AND, OR and NOT operators can also be represented as &&, || and !, respectively. The below are the same previous examples, by using the symbol operators:

mysql> SELECT 1 = 1 && 'test' = 'abc';

+-------------------------+
| 1 = 1 && 'test' = 'abc' |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT 1 = 1 || 'test' = 'abc';

+-------------------------+
| 1 = 1 || 'test' = 'abc' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT 1 != 1;

+--------+
| 1 != 1 |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

Operators in queries

Let us look at how these operators can be used in queries. The following query lists all records where the username is NOT john:

mysql> SELECT * FROM logins WHERE username != 'john';

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
3 rows in set (0.00 sec)

The next query selects users who have their id greater than 1 AND username NOT equal to john:

mysql> SELECT * FROM logins WHERE username != 'john' AND id > 1;

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)

Multiple Operator Precedence

SQL supports various other operations such as addition, division as well as bitwise operations. Thus, a query could have multiple expressions with multiple operations at once. The order of these operations is decided through operator precedence.

Here is a list of common operations and their precedence, as seen in the MariaDB Documentation:

Operations at the top are evaluated before the ones at the bottom of the list. Let us look at an example:

SELECT * FROM logins WHERE username != 'tom' AND id > 3 - 2;

The query has four operations: !=, AND, >, and -. From the operator precedence, we know that subtraction comes first, so it will first evaluate 3 - 2 to 1:

SELECT * FROM logins WHERE username != 'tom' AND id > 1;

Next, we have two comparison operations, > and !=. Both of these are of the same precedence and will be evaluated together. So, it will return all records where username is not tom, and all records where the id is greater than 1, and then apply AND to return all records with both of these conditions:

mysql> select * from logins where username != 'tom' AND id > 3 - 2;

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  2 | administrator | adm1n_p@ss | 2020-07-03 12:03:53 |
|  3 | john          | john123!   | 2020-07-03 12:03:57 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)

Exercise

TARGET: 94.237.122.95:52157
Authenticate to target with username "root" and password "password"

Challenge 1

In the 'titles' table, what is the number of records WHERE the employee number is greater than 10000 OR their title does NOT contain 'engineer'?

First login to the mysql database server:

──(macc㉿kaliLab)-[~]
└─$ mysql -u root -h 94.237.122.95 -P 52157 --skip-ssl -p

Output:

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.7.3-MariaDB-1:10.7.3+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

List the databases on the server using show databases:

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.110 sec)

Lets go ahead and use this database:

MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

List the tables found on the employees database:

MariaDB [employees]> SHOW tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.109 sec)

Take a look at the fields of the titles table using a simple SELECT:

MariaDB [employees]> SELECT * FROM titles LIMIT 5;
+--------+-----------------+------------+------------+
| emp_no | title           | from_date  | to_date    |
+--------+-----------------+------------+------------+
|  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  10002 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  10003 | Engineer        | 1986-12-01 | 1995-12-01 |
|  10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
|  10005 | Senior Staff    | 1996-09-12 | 9999-01-01 |
+--------+-----------------+------------+------------+
5 rows in set (0.247 sec)

Now write the following query to search for the number of records where the employee number greater than 10000 OR their title does NOT contain 'engineer'

SELECT * FROM titles WHERE emp_no > 10000 OR title NOT LIKE '%engineer%';

Output:

MariaDB [employees]> SELECT * FROM titles WHERE emp_no > 10000 OR title NOT LIKE '%engineer%';
+--------+--------------------+------------+------------+
| emp_no | title              | from_date  | to_date    |
+--------+--------------------+------------+------------+
|  10001 | Senior Engineer    | 1986-06-26 | 9999-01-01 |
|  10002 | Senior Engineer    | 1995-12-03 | 9999-01-01 |
|  10003 | Engineer           | 1986-12-01 | 1995-12-01 |
...
|  10651 | Assistant Engineer | 1988-12-29 | 1997-12-29 |
|  10652 | Engineer           | 1997-12-29 | 2000-11-15 |
|  10653 | Senior Staff       | 2000-03-12 | 9999-01-01 |
|  10654 | Staff              | 1992-03-12 | 2000-03-12 |
+--------+--------------------+------------+------------+
654 rows in set (0.290 sec)

flag: 654