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