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.
MySQL ISNULL
IS NULL is used in the WHERE clause to check if a value is NULL or not.
IS NULL in action.
MySQL Limit
The LIMIT clause, as you might guess, is used to limit the number of rows returned in the result set. It has two parameters or arguments. The first parameter is offset which tells mysql how many rows to skip before starting to retrieve. The second parameter is count which tells mysql how many rows to retrieve.
SELECT
column_name1,column_name2,...
FROM
table_name
LIMIT offset , count;
Here is an example.
As you can see here, the offset is 2 which means mysql will skip row one and two and start retrieving from row number 3. The count is 5 which means it will retrieve 5 rows. As a result, you see rows [3,4,5,6,and 7].
Offset is optional
The offset argument is optional. If you don’t use an offset then offset will be default to zero.
SELECT
column_name1,column_name2,...
FROM
table_name
LIMIT count;
The query above is the same as the query below.
SELECT
column_name1,column_name2,...
FROM
table_name
LIMIT 0, count;
Here is an example
LIMIT without the offset argument, just the count argument.
