joins and unions

Disclaimer: This presentation is prepared by trainees of baabtra as a part of mentoring program. This is not official document of baabtra –Mentoring PartnerBaabtra-Mentoring Partner is the mentoring division of baabte System Technologies Pvt . Ltd

JOINS AND UNION IN MySQLSHAMEEM ATatshameem89@hotmail.comwww.facebook.com/Shameem

Attwitter.com/ Shameem_atin.linkedin.com/in/ shameem

at+91-9446-986-108

JOINS AND UNION

INMySQL

JOIN• To query data from two or more tables, based on a relationship between

certain column in these tables.

• Tables in a database are often related to each other with keys.

• Primary key is a column (or a combination of columns) with a unique value for each row.

• Foreign Key in one table points to a Primary Key in another table.

Different JOINs in MySQL

INNER JOIN(JOIN):

Return rows when there is at least one match in both tables.

LEFT JOIN:

Return all rows from the left table, even if there are no matches

in the right table.

RIGHT JOIN:

Return all rows from the right table, even if there are no matches in the left table.

FULL JOIN:

Return rows when there is a match in one of the tables.

But It does not support in MySQL.

Tbl_dept

pk_dept_id

dept_name

1

CSE

2 ECE

3 EEE

4 CE

tbl_student

pk_student_id student_name student_place fk_dept_id

1 baabtra1 Calicut 1

2 baabtra2 cochin 2

3 baabtra3 Kannur 3

4 baabtra4 ooty 3

5 baabtra5 munnar 1

6 baabtra6 mlp NULL

INNER JOIN (JOIN)

• The INNER JOIN keyword returns rows when there is at least one match in both tables.

Syntax:

SELECT

column name(s) FROM

table_name1

INNER JOIN

table_name2 ON

table_name1.column_name=table_name2.column_name

Tbl_dept Tbl_student

INNER JOIN Example

student_name dept_name

baabtra1 CSE

baabtra5 CSE

baabtra2 ECE

baabtra3 EEE

baabtra4 EEE

SELECT tbl_student.student_name,tbl_dept.dept_nameFROM tbl_deptJOIN tbl_studentON tbl_dept.pk_dept_id = tbl_student.fk_dept_id;

LEFT JOIN• The LEFT JOIN keyword returns all rows from the left table (table_name1),

even if there are no matches in the right table (table_name2).

Syntax:

SELECT

column_name(s) FROM

table_name1 LEFT JOIN

table_name2 ON

table_name1.column_name=table_name2.column_name.

Tbl_dept Tbl_student

LEFT JOIN Example

SELECT tbl_student.student_name,tbl_dept.dept_name

FROM tbl_dept

LEFT JOIN tbl_student

ON tbl_dept.pk_dept_id = tbl_student.fk_dept_id; 

student_name dept_name

Baabtra1 CSE

Baabtra5 CSE

Baabtra2 ECE

Baabtra3 EEE

Baabtra4 EEE

NULL CE

RIGHT JOIN

• The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

Syntax:

SELECT

column_name(s) FROM

table_name1 RIGHT JOIN

table_name2 ON

table_name1.column_name=table_name2.column_name.

Tbl_dep Tbl_student

RIGHT JOIN Example

SELECT tbl_student.student_name,tbl_dept.dept_name

FROM tbl_dept

RIGHT JOIN tbl_student

ON tbl_dept.pk_dept_id = tbl_student.fk_dept_id;

Student_name Dept_name

Baabtra1 CSE

Baabtra2 ECE

Baabtra3 EEE

Baabtra4 EEE

Baabtra5 CSE

Baabtra6 NULL

FULL JOIN

• There is no FULL JOIN in MySQL.

• We can possible this by using both LEFT and RIGHT Joins together with UNION key word.

Tbl_dep Tbl_student

UNION

• The UNION operator combines two or more SELECT statements.

• The UNION operator is used to combine the result-set of two or more SELECT statements.

• Each SELECT statement within the UNION must have the same number of columns.

• The columns must also have similar data types.

• The columns in each SELECT statement must be in the same order.

UNION Syntax

SELECT column _ name(s)

FROM Table_name1

UNION

SELECT column _ name(s)

FROM table_name2

• The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.

UNION ALL

• The UNION operator selects only distinct values by default. If you need to allow duplicate values, use UNION ALL.

Syntax

SELECT column_name (s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2

UNION Example

SELECT tbl_student.student_name,tbl_dept.dept_name

FROM tbl_dept

LEFT JOIN tbl_student

ON tbl_dept.pk_dept_id = tbl_student.fk_dept_id

UNION

SELECT tbl_student.student_name,tbl_dept.dept_name

FROM tbl_dept

RIGHT JOIN tbl_student

ON tbl_dept.pk_dept_id = tbl_student.fk_dept_id;

Student_name Dept_name

Baabtra1 CSE

Baabtra5 CSE

Baabtra2 ECE

Baabtra3 EEE

Baabtra4 EEE

NULL CE

Baabtra6 NULL

UNION ALL Example

SELECT tbl_student.student_name,tbl_dept.dept_name

FROM tbl_dept

LEFT JOIN tbl_student

ON tbl_dept.pk_dept_id=tbl_student.fk_dept_id

UNION ALL

SELECT tbl_student.student_name,tbl_dept.dept_name

FROM tbl_dept

RIGHT JOIN tbl_student

ON tbl_dept.pk_dept_id=tbl_student.fk_dept_id;

Student_name Dept_name

Baabtra1 CSE

baabtra5 CSE

baabtra2 ECE

baabtra3 EEE

baabtra4 EEE

NULL CE

baabtra1 CSE

baabtra2 ECE

baabtra3 EEE

baabtra4 EEE

baabtra5 CSE

baabtra6 NULL

SELECT tbl_dept.dept_name

FROM tbl_dept

UNION

SELECT tbl_student.student_name

FROM tbl_student;

dept_name

CSE

ECE

EEE

CE

Baabtra1

Baabtra2

Baabtra3

Baabtra4

Baabtra5

baabtra6

THANKS

If this presentation helped you, please visit our page facebook.com/baabtra and like it.

Thanks in advance.

www.baabtra.com | www.massbaab.com |www.baabte.com

Contact Us