Reading Time: 7 minutes

1,075 Views

When we work with MySQL Queries, then definitely for some relational data we need to work with Joins. Inside this article we will see the concept of How To work with MySQL Joins.

Joins in MySQL is the connection between one and more tables to get data. In MySQL we have Inner join, Left join, Right join, Cross join etc.

We will see the concept of each join of MySQL in very clear way. We will consider database tables for this concept.

Learn More –

Let’s get started.

Types of Joins

As per the documentation we have following types of joins available –

Inner Join
Left Join
Right Join
Cross Join

Step by step we will see about each MySQL joins.

MySQL Inner Join

This join brings the data on the basis of a common value condition between two or more than two tables. According to matched condition it will bring all data what we expected for. It remove those rows from result set which has no matched condition.

Now,

Let’s create small program for it.

We will consider a employees table and a projects table. Fake data for these tables are –

Table: employees

Table: projects

Using concept of Inner join in MySQL –

MySQL Query –

select * from `employees` inner join `projects` on `projects`.`employee_id` = `employees`.`id`

Output

MySQL Left Join

This also works same the matched condition between two or more than two tables. But in this case we also get the rows of left table which doesn’t match with the condition with the right hand sided table. Means we get all rows of left table including values of matched with right table.

Now,

Let’s create small program for it.

We will consider a employees table and a projects table. Fake data for these tables are –

Table: employees

Table: projects

Using concept of Left join in MySQL –

MySQL Query –

select * from `employees` left join `projects` on `projects`.`employee_id` = `employees`.`id`

Output

MySQL Right Join

This works same the matched condition between two or more than two tables. But in this case we also get the rows of right table which doesn’t match with the condition with the left hand sided table. Means we get all rows of right table including values of matched with left table.

Now,

Let’s create small program for it.

We will consider a employees table and a projects table. Fake data for these tables are –

Table: employees

Table: projects

Using concept of Right join in MySQL –

MySQL Query –

select * from `employees` right join `projects` on `projects`.`employee_id` = `employees`.`id`

Output

MySQL Cross Join

Cross joins generate a Cartesian product between the first table and the joined table.

Now,

Let’s create small program for it.

We will consider a sizes table and a colors table. Fake data for these tables are –

Table: sizes

Table: colors

Using concept of Cross join in MySQL –

MySQL Query –

select * from `sizes` cross join `colors`

Output

We hope this article helped you to learn about How To work with MySQL Joins Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.