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

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

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%'

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

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;

Pros

Fast SELECT

Cons

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

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`;

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

Updates all rows. Increases every student's points by one

UPDATE `students` SET `points` = points+1;

Updates several columns in one row using

private key

UPDATE `students` SET `first_name` = 'თენგიზ', `last_name` = 'ბოჭორიშვილი', mail = 'tengiz@gmail.com' WHERE `students`.`id` = 38;

Updates students table based on notes table column -

notes.id

using

Implicit JOIN

UPDATE notes, students
SET points = points+1
WHERE notes.student_id = students.id
	AND notes.note = 'My first note'

Updates students table based on notes table column -

notes.id

using

explicit JOIN

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

Updates students table based on notes table column -

notes.id

using sub-query

UPDATE students 
SET points = points + 1 
WHERE student_id = (
        SELECT student_id 
        FROM notes 
        WHERE notes.id = 1
    )

Alternative of the above three updates:

SELECT student_id FROM `notes` WHERE id = 5;
UPDATE students SET points = points + 1 WHERE id = 3;

Select all students with points between 80 and 90 (using BETWEEN AND operator)

SELECT *
FROM students
WHERE points BETWEEN 80 AND 90

We can get the same result by using

AND

logical operator

SELECT *
FROM students
WHERE points >= 80
    AND points <= 90