MySQL join

MySQL  join :

“JOIN” is an SQL keyword which used to query data from two or more related tables.

MySQL, PostgreSQL, Firebird, SQLite, SQL Server and Oracle are relational database systems. A well-designed database provides a number of tables containing related data. You can see a very simple example :

Here students and course are two table :

id name course
1 pradeep 1
2 amit 1
3 rahul 2
4 Dev 5
5 Raman (NULL)

Course table:

id name
1 Java
2 C#
3 C++
4 C

In essence, MySQL will automatically:Since we’re using InnoDB tables and know that student.course and are related, we can specify a foreign key relationship:

  • re-number the associated entries in the user.course column if the changes
  • reject any attempt to delete a course where users are enrolled.

Note: This is terrible database design!

This database is not efficient. It’s fine for this example, but a student can only be enrolled on zero or one course. A real system would need to overcome this restriction — probably using an intermediate ‘enrollment’ table which mapped any number of students to any number of courses.

JOINs allow us to query this data in a number of ways.


Example :


FROM `user`

INNER JOIN `course` on user.course =;

Output :
Pradeep Java
amit Java
rahul C#


Left join :

What if we require a list of all students and their courses even if they’re not enrolled on one? A LEFT JOIN produces a set of records which matches every entry in the left table (user) regardless of any matching entry in the right table (course).


FROM `user`

LEFT JOIN `course` on user.course =;

Right Join :


FROM `user`

RIGHT JOIN `course` on user.course =;



MySQL having clause with group by clause

MySQL having clause with group by clause :

Syntax :

select * from <table_name> group by <column_name> having <condition>;

Example :

select * from employee group by salary having salary>45000;

MySQL sorting data from table

MySQL sorting data from table :

MySQL allow us to view data from table in a sorted order. The rows retrieved from the table will be sorted in either ascending order or descending order depending on given condition.
Note : to sort data as ascending use “asc” and descending use “desc”.

Syntax :

select * from <table_name> order by <column_name> <[sorting_order]>;

Example :

Suppose you want to view data from table as salary in descending order. So you will have to write query as follows…

select * from employee order by salary desc;


MySQL eliminating duplicate rows with select statement :

MySQL eliminating duplicate rows with select statement :

A table may contain duplicate rows and you need to view only unique row then you can use distinct keyword with
select statement.

Syntax :

select distinct * from <table_name>;

Example :

select distinct * from employee;