Subscribe To Our Newsletter
You will receive our latest post and tutorial.
Thank you for subscribing!

required
required


MySQL Sub Query

 

A sub query is a query within a query. A subquery can take place in a SELECT, FROM, and WHERE clause. A subquery can be used in a INSERT, UPDATE, SELECT, and DELETE query.

The main query is called the outer query and the subquery in called the inner query.

The sub query is executed and its results are passed to the outer query which is then executed after.

SELECT 
    column_name1, column_name2,....
FROM
    table_name1
WHERE
    column_name1 operator (SELECT 
            column_name
        FROM
            table_name2
        );

Here we are filter out rows with sales_rep_id that is less than the three smallest sales_rep_id which is, in this case, 3, 4, and 5.

Subquery in FROM clause

Subquery in the FROM clause forms a derived or temporary table. This derived or temporary table must have an alias.

As you can see, “(SELECT phone, credit_limit FROM customer) as tempCustomer” formed a derived table.

Correlated subquery

A correlated subquery is different from a regular subquery that stands alone. A correlated subquery uses columns or data from the outer query. It evaluates once for every row in the outer query.

Here is another example where we select all customers with address id that is greater than the average id of the employee.

 

March 31, 2019

MySQL Self Join

 

Self Join is designed to return rows with other rows within the same table. Table1 is joined with itself.

SELECT column_name1,column_name2,...
FROM table_name1 tab1, 
INNER JOIN table_name1 tab2 ON condition;

We are joining employees to their managers. You must use ALIAS for the table to distinguish the difference.

 

March 31, 2019

MySQL Cross Join

 

The CROSS JOIN is used to join all rows from one table to all rows of another table.

SELECT 
    *
FROM
    table_name1
CROSS JOIN
    table_name2;

 

March 31, 2019

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.

 

March 31, 2019

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.

 

 

March 31, 2019
Prev
Next