MySQL Joins Syntax Example

Learn how to write Joins in MySQL database ! (inner join, left join, right outer join, cross join examples)

In this tutorial you will how to join two tables in mysql! in following examples we will be using different type of joins to understand the impact on each result set.

Type of Joins in MySQL Database

To understand different type of joins in mysql database we need at least two tables and some data in both tables, so first let's create two tables and insert some data ... as defined below.

CREATE TABLE `tbadminuser` (
  `auid` int(10) UNSIGNED NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(150) NOT NULL,
  `createdate` datetime NOT NULL,
  `isActive` tinyint(1) NOT NULL
);
CREATE TABLE `tbadminprofile` (
  `apid` int(10) UNSIGNED NOT NULL,
  `auid` int(10) UNSIGNED NOT NULL,
  `fname` varchar(50) NOT NULL,
  `lname` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `mobile` varchar(45) NOT NULL
);

Now insert following records in above two tables.

We have inserted one matching record and one unmatched record, so you can understand the outcome of different type of joins

All joins will be based on relation tbadminuser.auid=tbadminprofile.auid

Insert into tbadminuser
(auid, username,password, createdate, isActive)
values
(1,'admin1','pass123', curdate(), 1);
Insert into tbadminProfile
(apid, auid,fname, lname, email, mobile)
values
(1,1,'Bill', 'Mark', 'webtrainingroom@gmail.com','900045785211');
Insert into tbadminuser
(auid,username,password, createdate, isActive)
values
(2, 'admin2','pass203', curdate(), 1);
Insert into tbadminProfile
(apid, auid, fname, lname, email, mobile)
values
(2, 3, 'Arin', 'Chak', 'aac@outlook.com','978911311001');

Here in example you will learn about how to write different type of joins in mysql database tables.

MySQL Inner Join
MySQL Left Join
MySQL Right Join
MySQL Cross Join

Inner Join Example

In Inner join we will get only common matching records, means based on the relation key we provide (tbadminuser.auid=tbadminprofile.auid) inner join will fetch only those records where key value will match

use database databaseName;

    
    SELECT
    t1.auid,
    t1.username,
    t1.password,
    t1.isActive,
    t2.apid,
    t2.fname,
    t2.lname,
    t2.email,
    t2.mobile
FROM
    tbadminuser AS t1
INNER JOIN tbadminprofile AS t2
ON
    t1.auid = t2.auid;
// working fine

As you can see in result set below, only common records from both tables has been fetched, when using inner join, most of the time we use this inner join.

mysql inner join explained

Left Join Example in mysql

Left join will fetch common matching records and also unmatched records from left table

    SELECT
    t1.auid,
    t1.username,
    t1.password,
    t1.isActive,
    t2.apid,
    t2.fname,
    t2.lname,
    t2.email,
    t2.mobile
FROM
    tbadminuser AS t1
LEFT JOIN tbadminprofile AS t2
ON
    t1.auid = t2.auid;
// working fine

Left join gets all matching records and also unmatched records from left table, this join often used for report generation, to find unmatched rows.

mysql left join explained

Mysql Right Join Example

Right join is just opposite to Left Join, Right join will fetch common matching records and also unmatched records from right table.

    
    SELECT
    t1.auid,
    t1.username,
    t1.password,
    t1.isActive,
    t2.apid,
    t2.fname,
    t2.lname,
    t2.email,
    t2.mobile
FROM
    tbadminuser AS t1
RIGHT JOIN tbadminprofile AS t2
ON
    t1.auid = t2.auid;
// working fine

As you can see the following result set is just opposite to above result set, right join is just opposite to left join, often used for report generation.

mysql right join explained

Cross Join Example

In CROSS join, the result set appeared by multiplying each row of the first table with all rows in the second table if no condition introduced with CROSS JOIN.

    
    SELECT
    t1.auid,
    t1.username,
    t1.password,
    t1.isActive,
    t2.apid,
    t2.fname,
    t2.lname,
    t2.email,
    t2.mobile
FROM
    tbadminuser AS t1
CROSS JOIN tbadminprofile AS t2

mysql cross join explained

MySQL Tutorial | SQL Course | SQL Interview Questions

MySQL Joins Examples

MySql Database Development Tutorials.