MySQL UNION and INTERSECTION operator




  1. MySQL UNION Operator:

MySQL UNION operator allow to combine two or more result sets from multiple tables into a single result set.

 

. Syntax:

Select  column1, column2

Union [Distinct | all]

Select column1, column2

Union [Distinct | all]
.
.
.

. Remember the following rules:

  1. The number of columns in the corresponding table should be same.
  2. 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.

union1

Example 2) without using alias.

union2

If you don’t use any alias name then MySQL choose first’s column as default.

Example 3) using union all

union3

Example 4) using order by:

union4

When you use order by, where, having clauses you must use brackets for each select statement.

for intersection operator next page.

Introducing to MySQL GROUP BY clause





The 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:

groupby1

The GROUP BY clause must appear after the FROM and 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:

groupby2

example 2

:groupby3

MySQL Subquery





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.
sql-subquery1

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

sql-subquery2
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.
sql-subquery3

MySQL self join





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.

self1

self2

MySQL Left &Right Join




  1. 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:

left-join-syntax

left-join2

 

  1. 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:

capture

right-join

 

 

 

SORTING DATA




  1. 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.

 

sorting1

MySQL INNER JOIN:





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.

55

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.44

MySQL alias:




  1. 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.

11

The keyword as is optional in the query .  If  the alias contains space, we have to use  a single quote for the alias.

22

A simple example of column alias is illustrated below:

33

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.

tablealias2

We use table alias with INNER JOIN , LEFT JOIN , RIGHT JOIN ,self-join,  and subqueries as well.

 

 

LIMIT clause:




  • LIMIT clause:

This clause is used in SELECT statement to constraint the numbers of rows in the result.
. Syntax:
SELECT col1, col2, …….
FROM table
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.
limit1

. Example 1: select first five employee.

limit2

.Example 2: Find first five employee whose salary is maximum then others.

limit3

OR like this way,

limit4

LIKE operator:





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.

like-1

  1. Example of %:
    • Select name that start from s in employee table.

like-2

  • Select name that end with a .

like-3

  • Select name that contain a.

like-4

    • Example of _:Show whose name start from ‘s’ and end with ‘a’ with 2 character between them.

like-5

Copyright © 2016 InfoxIT Portal. All rights reserved.
Seo wordpress plugin by www.seowizard.org.
Scroll To Top
Visit Us On FacebookVisit Us On Google PlusVisit Us On YoutubeCheck Our Feed