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
5 SQL

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

  • re-number the associated entries in the user.course column if the course.id 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 :


SELECT user.name, course.name

FROM `user`

INNER JOIN `course` on user.course = course.id;

Output :

 

user.name course.name
Pradeep Java
amit Java
rahul C#
Dec SQL

 

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


SELECT user.name, course.name

FROM `user`

LEFT JOIN `course` on user.course = course.id;

Right Join :


SELECT user.name, course.name

FROM `user`

RIGHT JOIN `course` on user.course = course.id;

 

Advertisements

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;