SQL Join Queries Tutorial

 

Joining tables in SQL

MySQL JOINS: In this tutorial, you are going to learn how to use different types of 

joins on tables. Below you will find different types of join queries with examples.

Types of JOINS in SQL

1. INNER JOIN
2.  OUTER JOIN 
1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. SELF JOIN
   

INNER JOIN –  Returns only all common rows from both the tables

 

TOC

 

 

Create following tables in order to test the query 

Use the queries below to create tables in MySQL:

 

Query -T1

 

--
-- Table structure for table `t1`
--

CREATE TABLE IF NOT EXISTS `t1` (
  `sno` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(30) NOT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `t1`
--

INSERT INTO `t1` (`sno`, `sname`) VALUES
(1, 'AAA'),
(2, 'BBB'),
(3, 'CCC');

 

 Query -T2 

 

 

TOP

--
-- Table structure for table `t2`
--

CREATE TABLE IF NOT EXISTS `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sno` int(11) NOT NULL,
  `total` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `t2`
--

INSERT INTO `t2` (`id`, `sno`, `total`) VALUES
(1, 2, 234),
(2, 3, 345),
(3, 4, 456),
(4, 5, 567); 

 

 

Join Syn:

 

SELECT select_column_list  

 

FROM table1 

 

INNER JOIN table2

 

ON < join_condition >;

Query:  

SELECT T1.SNO, T1.SNAME, T2.TOTAL
 

FROM T1  

INNER JOIN T2 

ON T1.SNO = T2.SNO;

O/P:

 

Go2TOP

 

OUTER JOIN

1. Left Outer Join / Left Join - 

Displays all the rows from the left table and common rows of both tables.

GotoTOC

 
SELECT T1.SNO, T1.SNAME, T2.TOTAL 
FROM T1  

LEFT JOIN T2 

ON T1.SNO = T2.SNO; 

O/P: 

 Below Venn diagram illustrates the outer join.

2. Right Outer Join  / Right Join 
 

 

 

GotoTOC

Returns all rows from the right table, and common rows of both tables.

SELECT T1.SNO, T1.SNAME, T2.TOTAL
 

FROM T1 RIGHT JOIN T2 

ON T1.SNO = T2.SNO;

O/P:

 

3. SELF JOIN

A table is joined with itself is known as SELF Join.

Query - EMPLOYEE 

TOP↑ 

Use the code below to create a table - EMPLOYEE:

--
-- Table structure for table `employee`
--

CREATE TABLE IF NOT EXISTS `employee` (
  `empno` int(11) NOT NULL,
  `employee` varchar(50) NOT NULL,
  `manager` varchar(15) NOT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`empno`, `employee`, `manager`) VALUES
(100, 'AAA', '130'),
(110, 'BBB', '150'),
(120, 'CCC', '140'),
(130, 'DDD', '140'),
(140, 'EEE', '100'),
(150, 'FFF', '110'); 

SELECT E.EMPNO, E1.EMPLOYEE AS EMPLOYEE, E.EMPLOYEE AS MANAGER  

FROM EMPLOYEE E, EMPLOYEE E1 

WHERE E.EMPNO = E1.MANAGER  

 TOPMenu

O/P: