MySQL Right Join
The RIGHT JOIN clause is used to join multiple tables, return all rows from the right table(table2) and the matched rows from the left table(table1). Non matched rows from table1 will return nulls.
SELECT
column_name1,column_name2,...
FROM table_name1
RIGHT JOIN table_name2 ON join_condition;
Here we are going to use RIGHT JOIN to show customers and their addresses.
MySQL Left Join
The LEFT JOIN clause is used to join two or more tables together. The main table will return all of its rows and the matching rows from the joining tables. If the joining tables don’t have the matching rows then MySQL will use NULL to represent those rows.
SELECT
t1.c1, t1.c2, t2.c1, t2.c2
FROM
main_table_name t1
LEFT JOIN
table2 t2 ON t1.c1 = t2.c1;
Basically, you are getting everything from the main table and the matching rows from the joining tables.
Note that if LEFT JOIN is used with WHERE or/and HAVING then all rows must satisfy all conditions.
MySQL Join
The INNER JOIN is used to query join multiple tables together. The join condition must match column values from both tables.
SELECT column_name1,column_name2,... FROM table_name1 INNER JOIN table_name2 ON join_condition1 INNER JOIN table_name3 ON join_condition2;
Basically you are getting what both tables have in common.
Here we are joining the customer table and the address table to show the customer address details.
For each row in the customer table, the INNER JOIN checks if the address_id exists in the column id of the address table. If it does then rows from both table are retrieved and show the columns you specify in the SELECT clause.
Now we are going use INNER JOIN to join 5 tables to show what customers have ordered. Note that for best performance you must limit the number of tables to join. The more tables you join the slower the query tends to perform.
INNER JOIN with a complex join condition
Here we add another condition where zipcode >= 78704.
MySQL Order By
The ORDER BY clause is used to sort the result set which is not sorted by default. You can sort the result set by one or multiple columns and either in the ascending or descending order.
SELECT
column_name1, column_name2,...
FROM
table_name
ORDER BY
column_name1 [ASC|DESC], column_name2 [ASC|DESC],...
ASC – sort in ascending order
DESC – sort in descending order
* by default if [ASC|DESC] is omitted then ASC is used.
As you can see, our query is sorted by vender and ASC | DESC was omitted but MySQL sorted the result set in the ascending order(ASC).
ORDER BY with ASC and DESC
Here our query is sorted by vendor in ASC and price in DESC.
ORDER BY with custom order field
Using the FIELD() function you can sort the result set by column values.
MySQL Between
The BETWEEN is used in the WHERE clause to check if a value is in a range.
Here is an example of BETWEEN and AND. We are querying customers that have sales rep with id between 1 and 7.
BETWEEN can also be done by using >= and <=.
Here is an example of BETWEEN with NOT
BETWEEN with Date comparison
It is best practice to convert the value to the DATE datatype before using it with the BETWEEN clause.
