Categorize students based on their points using

CASE WHEN

conditional statement

SELECT *, 
    CASE 
      	WHEN points>90 THEN "Brilliant"
      	WHEN points>80 THEN "Gold"
      	WHEN points>60 THEN "Silver"
      	ELSE "Lazy"
    END as 'class'
FROM `students`
ORDER BY points DESC

Conditionally checking columns Using

CASE

SELECT * 
FROM `students` 
WHERE 
    (CASE
        WHEN LENGTH(mail) THEN mail
        ELSE mail2
    END) LIKE '%gmail.com%'
ORDER BY points

CASE

in

ORDER BY

clause (if the current day of month is odd, orders by

last_name

, otherwise orders by

first_name

SELECT *
FROM students
ORDER BY (
    CASE DAY(CURDATE())%2
      	WHEN 0 THEN first_name
      	WHEN 1 THEN last_name
    END
) DESC

Creates

index

for

guid_field

column

CREATE INDEX guid_field ON students123 (guid_field);

Removes

guid_field

column

index

ALTER TABLE `students123` DROP INDEX `guid_field`;

Pros

Fast SELECT

Cons

Slow UPDATE, INSERT, DELETE
Hard to maintain
Needs a lot of space

Without guid_field INDEX Tests against SELECT statements. Selects Individual rows using GUID field. Time comparison:

0.0020 - 0.0037 seconds

/

35 - 36 seconds

SELECT * FROM `students123` WHERE guid_field = '90f2c8af-7ead-11eb-9a81-b4b52f79163f'
SELECT * FROM `students123` WHERE guid_field = '90f2c35d-7ead-11eb-9a81-b4b52f79163f'

Selects all rows and orders by the GUID field. Time comparison:

0.0020 seconds

/

35 - 37 seconds

SELECT * FROM `students123` ORDER BY `students123`.`guid_field` DESC

Tests against INSERT statements. Inserts 1.000.000 rows. Time comparison:

34.1974 - 44.1248 seconds

/

18.3200 - 17.4967 seconds

INSERT INTO `students123` (guid_field, student_id, first_name, last_name, points)
SELECT UUID(), student_id, first_name, last_name, points FROM students123 WHERE id < 1000001

Tests against UPDATE statements. Updates 1M rows. Time comparison:

57.2683 - 66.9459 seconds

/

10.2285 - 12.5088 seconds

UPDATE `students123` SET guid_field = UUID() WHERE id > 1000000 AND id < 2000001

Tests against DELETE statements. Deletes 1M rows. Time comparison:

70.3673 - 76.8969 seconds

/

7.1480 - 19.2191 seconds

DELETE FROM `students123` WHERE id > 1000000 AND id < 2000001

To create the table and run the above tests

CREATE TABLE `students123` (
  `id` int(11) NOT NULL,
  `guid_field` varchar(100) NOT NULL,
  `student_id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `points` int(11) NOT NULL,
  `mail` varchar(255) DEFAULT NULL,
  `mail2` varchar(255) NOT NULL,
  `santa_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Select students with

gmail

account. Using

IF

statement:

SELECT * 
FROM `students` 
WHERE IF(LENGTH(mail), mail, mail2) LIKE '%gmail.com%'
ORDER BY points

Using

OR

logical operator:

SELECT *
FROM  `students` 
WHERE 
    (`mail` LIKE '%gmail.com%' AND LENGTH(mail))
    OR 
    (mail2 LIKE '%gmail.com%') 
ORDER BY points

Using

CASE

SELECT * 
FROM `students` 
WHERE 
    (CASE
        WHEN LENGTH(mail) THEN mail
        ELSE mail2
    END) LIKE '%gmail.com%'
ORDER BY points

IF

has three parameters: 1 - Condition. 2 - Executes if the condition evaluates to

true

3 - Executes if the condition evaluates to

false

IF(condition , [expression when true], [expression when false]);

Returns

false

because the first parameter

0

evaluates to false

SELECT IF(0, 'true', 'false') AS Boolean

Simple

IF

statement in

SELECT

clause

SELECT *, 
    IF(points>=90, "Brilliant", "Lazy") AS class
FROM `students`
ORDER BY points DESC

Nested IF

conditional statement. Categorizes students based on their points

SELECT *, 
    IF(points>=90, "Brilliant", IF(points>=80, "Gold", IF(points>=60, "Silver", "Lazy"))) AS class
FROM `students`
ORDER BY points DESC

Sub-query inside

IF

. Highlights the student with highest points

SELECT *, 
    IF(points>=90, IF(points=(SELECT MAX(points) FROM students), "Highest", "Brilliant"), IF(points>=80, "Gold", IF(points>=60, "Silver", "Lazy"))) AS class
FROM `students`
ORDER BY points DESC

IF

statement in

WHERE

clause. Checks

mail

if it is not empty otherwise checks

mail2

SELECT * 
FROM `students` 
WHERE IF(LENGTH(mail), mail, mail2) LIKE '%gmail.com%'

Categorizes students based on their points

SELECT *, 
    IF(points>=90, "Brilliant", IF(points>=80, "Gold", IF(points>=60, "Silver", "Lazy"))) AS class
FROM `students`
ORDER BY points DESC

The same result using

UNION

keyword

SELECT *, 'Brilliant' AS class
FROM `students`
WHERE points >= 90
UNION
SELECT *, 'Gold'
FROM `students`
WHERE points >= 80 
    AND points < 90
UNION
SELECT *, 'Silver'
FROM `students`
WHERE points >= 60 
    AND points < 80
UNION
SELECT *, 'Lazy'
FROM `students`
WHERE points < 60
ORDER BY points DESC

The same result using

CASE WHEN

conditional statement

SELECT *, 
    CASE 
      	WHEN points>90 THEN "Brilliant"
      	WHEN points>80 THEN "Gold"
      	WHEN points>60 THEN "Silver"
      	ELSE "Lazy"
    END as 'class'
FROM `students`
ORDER BY points DESC

NATURAL

join tries to find the fields with the same name and joins them

SELECT 
    students.*,
    notes.*
FROM `notes`
NATURAL JOIN `students` 

Note: using

NATURAL

join is dangerous, because we don't know explicitly which fields will be joint.

SELECT students.*, notes.* 
FROM `notes` 
JOIN `students`
	ON notes.student_id = students.student_id
    AND notes.id = students.id

Returns the same result as the above query

CROSS JOIN

is the same as

INNER

or

implicit

joins. Without conditions, both result in Cartesian product:

SELECT 
    students.*,
    notes.*
FROM `notes`
CROSS JOIN `students` 

JOIN

example:

SELECT 
    students.*,
    notes.*
FROM `notes`
JOIN `students` 

JOIN

is the short form of

INNER JOIN

SELECT 
    students.*,
    notes.*
FROM `notes`
INNER JOIN `students` 

Implicit

join example

SELECT 
    students.*,
    notes.*
FROM `notes`, `students` 

Both the queries return the exact same result

When we need to join a table to itself, it is called

SELF JOIN

SELECT  
    s1.*,
    s2.first_name
FROM `students` s1
JOIN `students` s2 ON s2.santa_id = s1.id

This type of join is often used when we have tree structure in a table. Note:

alias

is mandatory in

SELF JOIN