If the fields names that we want to join are the same, we can use

USING

keyword

SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT JOIN notes USING(student_id)

The alternative of the above query using

ON

clause:

SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.student_id

There is no such

FULL JOIN

in MySQL but we can get the same result using

UNION

SELECT 
    students.*, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
UNION
SELECT 
    students.*, 
    notes.*
FROM `students` 
RIGHT JOIN notes ON notes.student_id = students.id

As default,

UNION

removes duplicate rows. If we want to get all the rows including duplicates, we can use

UNION ALL

SELECT 
    students.*, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
UNION ALL
SELECT 
    students.*, 
    notes.*
FROM `students` 
RIGHT JOIN notes ON notes.student_id = students.id

Divides all the students into different classes

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

There are two types of

OUTER

JOINS: 1. LEFT JOIN - same as

LEFT OUTER JOIN

2. RIGHT JOIN - same as

RIGHT OUTER JOIN

LEFT JOIN example

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

These two joins are the same, they produce the same result

LEFT OUTER JOIN

example:

SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT OUTER JOIN notes ON notes.student_id = students.id

Note: The same goes for

RIGHT JOIN

and

RIGHT OUTER JOIN

, they produce the same result

Implicit JOIN syntax

SELECT 
    students.first_name,
    notes.*
FROM  
    `notes`,
    `students`
WHERE notes.student_id = students.id

The following explicit JOIN returns the same result as the above implicit one:

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

If we forget

WHERE

clause on implicit JOIN, we will get CROSS JOIN result

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

Which is the equivalent of the following explicit JOIN syntax:

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

CONS

of the implicit JOIN is that chance is higher to forget

WHERE

clause

LIMIT

clause format: Starts from

X

rows, Selects

Y

rows

-- LIMIT Y
-- LIMIT X Y

LIMIT

clause is used to limit the number of rows returned as a result

SELECT
    id,
    first_name
FROM students
LIMIT 2

LIMIT

clause for pagination. If two arguments are passed to LIMIT clause, the first one

offset

, which indicates how many rows we want to skip. In this example, we want to skip the first 10 records and get the following 5 records. In this case we want to get 3'th page

SELECT 
    first_name
    last_name
FROM students
ORDER BY points DESC
LIMIT 10, 5

Selects the student with the highest points

SELECT *
FROM students
ORDER BY points DESC
LIMIT 1

ASC

is optional because it's default value

ORDER BY

clause in

SELECT

statement

SELECT first_name, last_name 
FROM students
ORDER BY id DESC

Orders the result by

last_name

with

ascending

order

SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC

ORDER BY

with several fields

SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC, first_name ASC

Grouped by

last_name

and Ordered by

Average points

SELECT 
    last_name,
    AVG(points) average_points
FROM students
GROUP BY last_name
ORDER BY average_points DESC

Mathematical expression used to order the result

SELECT *
FROM `orders` 
ORDER BY price * quantity DESC

Order the result using

alias

SELECT *, price * quantity AS total
FROM `orders` 
ORDER BY total DESC

Orders the result by first name with

ASC

and by id with

DESC

using column numbers

SELECT
    id,
    first_name
FROM `students`
ORDER BY 2, 1 DESC

^

indicates the beginning of a string

&

indicates the end of a string

|

is used to represent multiple search patterns (logical OR)

[]

is used to represent any of the listed characters

[abcdef]

[-]

is used to represent any characters from the range

[a-g]

Select all students where

last_name

contains

უა

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP 'უა'

Select all students where

last_name

starts with

გე

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP '^გე'

Select all students where

last_name

ends with

უა

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP 'უა$'

Select all students where

last_name

contains

უა

or

ია

SELECT *
FROM students
WHERE last_name REGEXP 'უა|ია'

Select all students where

last_name

contains

უა

or

ია

using square brackets

SELECT *
FROM students
WHERE last_name REGEXP '[იუ]ა'

Select all students where

last_name

ends with any one letter from the range

[ა-უ]

followed by

ა

using square brackets with range

SELECT *
FROM students
WHERE last_name REGEXP '[ა-უ]ა$'

Select all students where

last_name

contains

ვა

or ends with

ია

or starts with

ცი

SELECT *
FROM students
WHERE last_name REGEXP '^ცი|ია$|ვა'

%

- represents any number of characters

_

- represents one single character Select all students which lastname contains 'უა' with

LIKE

operator

SELECT *
FROM students
WHERE last_name LIKE '%უა%'

Select all students which

lastname

ends with 'უა' using

LIKE

operator

SELECT *
FROM students
WHERE last_name LIKE '%უა'

Select all students which

lastname

does not contain 'უა' with

NOT LIKE

operator

SELECT *
FROM students
WHERE last_name NOT LIKE '%უა%'

Select all students where

lastname

starts with any two character followed with

უა

and we don't care what the other following symbols are (with

_

)

SELECT *
FROM students
WHERE last_name LIKE '__უა%'

Select all students where

lastname

is exactly 5 characters long and the second symbol is

უ

SELECT *
FROM students
WHERE last_name LIKE '_უ___'

Select all students where

lastname

is exactly 5 characters, starts with

a

and ends with

y

and has exacly any three letters between them

SELECT *
FROM students
WHERE last_name LIKE 'a___y'

Select all students where

lastname

ends with either

ძე

or

შვილი

SELECT *
FROM students
WHERE last_name LIKE '%ძე' OR last_name LIKE '%შვილი'

Deletes all the records FROM

notes

table. This is too dangerous command because there is no undo when it comes to databases. If we update or delete something, the original values are gone.

DELETE FROM notes;

Deletes the row with the specified private key -

id

. We should always use

WHERE

clause, not to delete everything from the database

DELETE FROM `requests` WHERE `id` = '1548630';

Deletes the student with the specified first name and last name

DELETE FROM `requests` WHERE (first_name = 'John' AND last_name = 'Doe');

following code will return users with their latest published post

$latestPosts = DB::table('posts')
                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->where('is_published', true)
                   ->groupBy('user_id');

$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })->get();