{ Ellis Min }

[SQL]: Logical Operators

SQL

09/28/2019

Documentation on Comparison functions and operators

Students data available from

students.sql
students.txt

NOT Equal

SQL
SELECT major FROM students
WHERE major != "Computer Science";
TEXT
+--------------+
| major        |
+--------------+
| Biology      |
| Linguistics  |
| Chemistry    |
| Accounting   |
| Business     |
| Biochemistry |
| Physics      |
| Art          |
| Biology      |
| no_major     |
| Japanese 10% |
| Food Science |
| Economics    |
+--------------+

Shows all majors except for Computer Science. Case Sensitive.

NOT LIKE

SQL
SELECT fname, age FROM students
WHERE age NOT LIKE '2%';
TEXT
+--------+------+
| fname  | age  |
+--------+------+
| Ellis  |   18 |
| John   |   19 |
| Lamont |   18 |
| Diego  |   17 |
| Mary   |   16 |
| Lena   |   31 |
| Ellis  |   18 |
+--------+------+

Show ages that are not twenties. Better practice may be using range (upcoming)

Greater & Less than

SQL
SELECT fname, credit FROM students
WHERE credit < 50;
TEXT
+----------+--------+
| fname    | credit |
+----------+--------+
| Ellis    |     13 |
| John     |     21 |
| Brent    |     11 |
| Lamont   |     37 |
| Diego    |      6 |
| Angelina |     41 |
| Brad     |     13 |
| Mary     |     25 |
+----------+--------+
SQL
SELECT CONCAT(fname, ' ', lname) AS fullname, age
FROM students
WHERE age >= 25;
TEXT
+------------------+------+
| fullname         | age  |
+------------------+------+
| Sam Smith        |   25 |
| Angelina Randall |   26 |
| Dan Joyce        |   27 |
| Lena Shah        |   31 |
+------------------+------+

Shows full name of students whose age is greater than or equal to 25 inclusive

Boolean

SQL
SELECT 99 > 1;
SELECT -15 > 15;
TEXT
+--------+
| 99 > 1 |
+--------+
|      1 |
+--------+
+----------+
| -15 > 15 |
+----------+
|        0 |
+----------+

1 represents True and 0 represents false

SQL
SELECT 'a' > 'b'; -- false
SELECT 'a' < 'b'; -- true
SELECT 'A' > 'a'; -- false
SELECT 'A' >= 'a'; --String comparison is case insensitive
TEXT
+-----------+
| 'a' > 'b' |
+-----------+
|         0 |
+-----------+
+-----------+
| 'a' < 'b' |
+-----------+
|         1 |
+-----------+
+-----------+
| 'A' > 'a' |
+-----------+
|         0 |
+-----------+
+------------+
| 'A' >= 'a' |
+------------+
|          1 |
+------------+

Avoid doing string comparison!

AND (&&)

SQL
SELECT fname, credit FROM students
WHERE lname='Min' AND credit=13;
SELECT fname, credit FROM students
WHERE lname='Min' && credit=13;
TEXT
+-------+--------+
| fname | credit |
+-------+--------+
| Ellis |     13 |
+-------+--------+

Both yields the same result

AND & LIKE

SQL
SELECT fname, major, age, credit FROM students
WHERE age > 20 && credit < 200 &&
major NOT LIKE '%science%';

Show students older than 20, has less than 200 credits, and major doesn't include "science"

OR(||)

SQL
SELECT fname, major FROM students
WHERE major LIKE "%sCienCE" OR
major="bioLOGY";
---------
SELECT fname, major FROM students
WHERE major LIKE "%sCienCE" ||
major="bioLOGY";
TEXT
+--------+------------------+
| fname  | major            |
+--------+------------------+
| Ellis  | Computer Science |
| Tenzin | Biology          |
| Sam    | Computer Science |
| Brad   | Biology          |
| Lena   | Food Science     |
+--------+------------------+

Again case insensitive. Both yields the same result where it shows students that study biology OR major that includes "science"

BETWEEN

SQL
SELECT fname, age
FROM students
WHERE age >= 20 AND age < 23;
---
SELECT fname, age FROM students
WHERE age BETWEEN 20 AND 22;
TEXT
+----------+------+
| fname    | age  |
+----------+------+
| Tenzin   |   22 |
| Brent    |   22 |
| Jerimiah |   20 |
| Brad     |   22 |
+----------+------+

Both yields the same result--shows students between age of 20 and 22 inclusive. BETWEEN is inclusive!

NOT BETWEEN

SQL
SELECT fname, age FROM students
WHERE age NOT BETWEEN 18 and 23;
TEXT
+----------+------+
| fname    | age  |
+----------+------+
| Sam      |   25 |
| Diego    |   17 |
| Angelina |   26 |
| Dan      |   27 |
| Mary     |   16 |
| Lena     |   31 |
+----------+------+

Shows students whose age is not in between 18 and 23.

Side note

When using BETWEEN with date of time, use CAST() to explicitly convert the values to the desired data types. For example, convert DATE value to DATETIME value for comparison

CAST()

Converts data types into desired ones

SQL
SELECT CAST('2011-05-05' AS DATETIME);
TEXT
+--------------------------------+
| CAST('2011-05-05' AS DATETIME) |
+--------------------------------+
| 2011-05-05 00:00:00            |
+--------------------------------+

Converts string to DATETIME data type

Example

TEXT
+-------+------------+-----------+---------------------+
| name  | birthdate  | birthtime | birthdt             |
+-------+------------+-----------+---------------------+
| Paul  | 1956-11-13 | 10:03:33  | 1956-11-13 10:03:33 |
| Larry | 1967-12-23 | 04:20:47  | 1967-12-23 04:20:47 |
| John  | 2019-09-29 | 00:44:35  | 2019-09-29 00:44:35 |
+-------+------------+-----------+---------------------+
SQL
SELECT name, birthdt FROM ppl
WHERE birthdt BETWEEN '1960-01-01' AND '2000-03-03';

Above query sill works because MySQL is smart enough to auto convert string to correct data type

SQL
SELECT name, birthdt FROM ppl
WHERE birthdt BETWEEN CAST('1960-01-01' AS DATETIME)
AND CAST('2000-03-03' AS DATETIME);

This one is correct

TEXT
+-------+---------------------+
| name  | birthdt             |
+-------+---------------------+
| Larry | 1967-12-23 04:20:47 |
+-------+---------------------+

IN & NOT IN

SQL
SELECT fname FROM students
WHERE fname='ellis' || fname='john' || fname='diego';
-- SAME AS..
SELECT fname FROM students
WHERE fname IN ('ellis', 'john', 'diego');
TEXT
+-------+
| fname |
+-------+
| Ellis |
| John  |
| Diego |
| Ellis |
+-------+

Both do the same. Using IN is more concise

SQL
SELECT age from students
WHERE age
NOT IN(16, 18, 20, 22, 24, 26, 28);
-- SAME result AS..
SELECT age from students
WHERE age % 2 != 0;
TEXT
+------+
| age  |
+------+
|   25 |
|   19 |
|   23 |
|   17 |
|   27 |
|   31 |
+------+

Both yields same results

CASE

SQL
SELECT fname, lname, age
    ,CASE
        WHEN age >=10 THEN 'Teenage'
        WHEN age >=20 THEN 'Twenties'
        ELSE 'somethingElse'
    END AS agetype
FROM students;
TEXT
+----------+----------+------+---------+
| fname    | lname    | age  | agetype |
+----------+----------+------+---------+
| Ellis    | Min      |   18 | Teenage |
| Tenzin   | Pema     |   22 | Teenage |
| Sam      | Smith    |   25 | Teenage |
| John     | Johnson  |   19 | Teenage |
| Dave     | Smith    |   23 | Teenage |
| Brent    | Lang     |   22 | Teenage |
| Jerimiah | Tucker   |   20 | Teenage |
| Lamont   | Marks    |   18 | Teenage |
| Diego    | Miller   |   17 | Teenage |
| Angelina | Randall  |   26 | Teenage |
| Brad     | Johnson  |   22 | Teenage |
| Dan      | Joyce    |   27 | Teenage |
| Mary     | Fletcher |   16 | Teenage |
| Lena     | Shah     |   31 | Teenage |
| Ellis    | Min      |   18 | Teenage |
+----------+----------+------+---------+

Above example is not expected. We can see that second WHEN is like else if statement. It won't be executed since the first statement was true

SQL
SELECT fname, lname, age
    ,CASE
        WHEN age BETWEEN 10 AND 19 THEN "Teenage"
        WHEN age BETWEEN 20 AND 29 THEN "Twenties"
        ELSE 'idk'
     END AS agetype
FROM students;
-- MORE SUCCINT
SELECT fname, lname, age
   ,CASE
    WHEN age < 20 THEN "Teenage"
    WHEN age < 30 THEN "Twenties"
    ELSE "idk"
    END AS agetype
FROM students;
TEXT
+----------+----------+------+----------+
| fname    | lname    | age  | agetype  |
+----------+----------+------+----------+
| Ellis    | Min      |   18 | Teenage  |
| Tenzin   | Pema     |   22 | Twenties |
| Sam      | Smith    |   25 | Twenties |
| John     | Johnson  |   19 | Teenage  |
| Dave     | Smith    |   23 | Twenties |
| Brent    | Lang     |   22 | Twenties |
| Jerimiah | Tucker   |   20 | Twenties |
| Lamont   | Marks    |   18 | Teenage  |
| Diego    | Miller   |   17 | Teenage  |
| Angelina | Randall  |   26 | Twenties |
| Brad     | Johnson  |   22 | Twenties |
| Dan      | Joyce    |   27 | Twenties |
| Mary     | Fletcher |   16 | Teenage  |
| Lena     | Shah     |   31 | idk      |
| Ellis    | Min      |   18 | Teenage  |
+----------+----------+------+----------+

Below is more concise and preferred but be sure to avoid previous mistakes

WHEN && OR

SQL
SELECT fname, lname,
    CASE
        WHEN major='Biology' || major LIKE "%science"
        THEN "bio or science"
        ELSE "others"
    END AS "bio&science"
FROM students;
TEXT
+----------+----------+----------------+
| fname    | lname    | bio&science    |
+----------+----------+----------------+
| Ellis    | Min      | bio or science |
| Tenzin   | Pema     | bio or science |
| Sam      | Smith    | bio or science |
| John     | Johnson  | others         |
| Dave     | Smith    | others         |
| Brent    | Lang     | others         |
| Jerimiah | Tucker   | others         |
| Lamont   | Marks    | others         |
| Diego    | Miller   | others         |
| Angelina | Randall  | others         |
| Brad     | Johnson  | bio or science |
| Dan      | Joyce    | others         |
| Mary     | Fletcher | others         |
| Lena     | Shah     | bio or science |
| Ellis    | Min      | others         |
+----------+----------+----------------+

IF

SQL
SELECT fname, lname,
IF(major="biology" || major LIKE "%science", "bio or science", "others") AS "bio&science"
FROM students;
  • Yields the same result as previous query
  • IF(condition, then.., else)

WRITTEN BY

Ellis Min

Keeping a record

GitHub