- MySQL UNION Operator:
MySQL UNION operator allow to combine two or more result sets from multiple tables into a single result set.
Select column1, column2
Union [Distinct | all]
Select column1, column2
Union [Distinct | all]
. Remember the following rules:
- The number of columns in the corresponding table should be same.
- The columns of each table should have same datatype to corresponding table.
. Different between union and union all:
Union does not return duplicate value (means it finds duplicate values and use distinct) where union all returns duplicate value.
.Examples.. Database name product , table names producttable1 and producttable2
Producttable1 has attribute produtname and price and
Prducttable2 has attribute pname and pprice.
Example 1) with using alias.
Example 2) without using alias.
If you don’t use any alias name then MySQL choose first’s column as default.
Example 3) using union all
Example 4) using order by:
When you use order by, where, having clauses you must use brackets for each select statement.
for intersection operator next page.
GROUP BY clause,groups a set of rows into a set of summary rows by values of columns or expressions. The
GROUP BY clause returns one row for each group.We use the
GROUP BY clause with aggregate functions such as SUM, AVG, MAX, MIN, andCOUNT.
The following illustrates the
GROUP BY clause syntax:
GROUP BY clause must appear after the
WHERE clauses. Following the
GROUP BY keyword is a list of comma-separated columns or expressions that you want to use as criteria to group rows.
MySQL GROUP BY examples
Simple MySQL GROUP BY example
we use the
GROUP BYclause with the name column as the following query:
MySQL subquery is simply using a query within another query. In MySQL subquery we nest a
query (inner query) within another query (outer query) and use the result of the inner query
for the outer query.
Let us look at a simple example.
In the given query, the inner query selects the eid whose dep_head is kaliya and the outer
query selects the eid,name and salary associated to that eid whose dep_head is kaliya.
MySQL Subquery within WHERE clause
We can use comparision operators =,<,> to compare the single value returned by the subquery
within WHERE clause.
let’s have a look at a simple example.
A self join is special case of join statement where we join a table to itself. In self join , we must use table alias to identify left table and right table of the same table. We use self join to combine row to other rows of the same table.
A simple example of self join is illustrated below.
- MySQL LEFT JOIN
When we join a table t1 with table t2 using left join , if a row from left table matches row from right table based on join condition , the row is selected in the result set. If no matches is found then row with left table is selected while making the row of left table null.
Syntax of left join:
- MySQL RIGHT JOIN
When we join a table t1 with table t2 using right join, if a row from right table matches row from left table based on join condition , the row is selected in result set. If there is no match in row from left table to the row from right table ,then row from right table is selected making the row from left table null.
Syntax of right join:
- ORDER BY
The ORDER BY clause allows to,
Sort a result set by a single column or multiple columns.
Sort a result set by different columns in ascending or descending order.By default, the ORDER BY clause sorts the result set in ascending order if you don’t specify ASC or DESC explicitly.
MySQL INNER JOIN clause matches rows in one table to rows in other table and allows us to query rows that contains columns from both table.
A syntax of MySQL INNER JOIN is shown below.
The rows in t1 and t2 both have to be matched on the basis of join condition and the matched rows are selected . If no match is found then it returns an empty set.
- MySQL column alias
The column names sometimes become more technical making the query difficult to understand. For this reason we use column alias.
A simple syntax of column alias is given below.
The keyword as is optional in the query . If the alias contains space, we have to use a single quote for the alias.
A simple example of column alias is illustrated below:
2.MySQL table alias
We can give a different name to a table as well like in column .
A syntax of table alias is shown below.
We use table alias with INNER JOIN , LEFT JOIN , RIGHT JOIN ,self-join, and subqueries as well.
This clause is used in SELECT statement to constraint the numbers of rows in the result.
SELECT col1, col2, …….
LIMIT offset, count;
Where, offset specifies first row to return. The offset of row start from 0 ,not 1.
And count specifies maximum number of rows to return.
. Example 1: select first five employee.
.Example 2: Find first five employee whose salary is maximum then others.
OR like this way,
It is used to select the data from table based on specific pattern.It is used in WHERE clause of SELECT statement.
There are two wild card used in LIKE operator i.e.% and _.
. % : Percentage is used to match zero or more character. Eg, ‘S%’ gives the data staring from S. That can be like Sam, Sameer, Sajhan ,etc.
. _: Underscore is used to match one character.Eg.’r_n’: ran, run.
Here is employee table with their attribute eid, name, address, salary, gender, phoneno and mid.
- Example of %:
- Select name that start from s in employee table.
- Select name that end with a .
- Select name that contain a.
- Example of _:Show whose name start from ‘s’ and end with ‘a’ with 2 character between them.