Understanding SQL JOINS Using PHP

Rating: 4.0. From 3 votes.
Please wait...

SQL Joins is one of the most challenging topics nowdays for the developers in programming. so PHPHURDLES is here with an easily understandable tutorial of SQL joins.
JOIN” is an SQL keyword used to query data from two or more related tables.This concept is regularly explained by various peoples using hard terms and examples, which confuses developers. , so this is an attempt by PHPHURDLES to explain JOINs briefly with a demo so that our young developers can easily understand it.

so lets starts with a new hurdle

firstly we will create two tables
1)student

MYSQL Table creation code:

CREATE TABLE `student` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`course` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

The course number relates to a subject being taken in a course table…

2)course

MYSQL Table creation code:

CREATE TABLE `course` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

joins allows us to query data in number of ways

Now we will go through some of them one by one..

INNER JOIN (or just JOIN) 

 

The most frequently used clause is INNER JOIN. This produces a set of records which match in both the student and course tables, i.e. all students who are enrolled on a course:

1
2
3
SELECT student.name name1, course.name name2
FROM `student`
INNER JOIN `course` on student.course = course.id;//name1 and name2 are alias name used because both tables have same column name therefore for fetching results we used alias name

LEFT JOIN

LEFT JOIN is used 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 (student) regardless of any matching entry in the right table (course):

1
2
3
SELECT student.name name1, course.name name2
FROM `student`
LEFT JOIN `course` on student.course = course.id;//name1 and name2 are alias name used because both tables have same column name therefore for fetching results we used alias name

RIGHT JOIN

Right Join is used when we require a list all courses and students even if no one has been enrolled? A RIGHT JOIN produces a set of records which matches every entry in the right table (course) regardless of any matching entry in the left table (student):

1
2
3
SELECT student.name name1, course.name name2
FROM `student`
RIGHT JOIN `course` on student.course = course.id;//name1 and name2 are alias name used because both tables have same column name therefore for fetching results we used alias name

OUTER JOIN (or FULL OUTER JOIN)

OUTER JOIN which returns all records in both tables regardless of any match. Where no match exists, the missing side will contain NULL.

OUTER JOIN is less useful than INNER, LEFT or RIGHT and it’s not implemented in MySQL. However, you can work around this restriction using the UNION of a LEFT and RIGHT JOIN, e.g.

1
2
3
4
5
6
7
8
9
SELECT student.name name1, course.name name2
FROM `student`
LEFT JOIN `course` on student.course = course.id

UNION

SELECT student.name name1, course.name name2
FROM `student`
RIGHT JOIN `course` on student.course = course.id;//name1 and name2 are alias name used because both tables have same column name therefore for fetching results we used alias name

This is how we can use SQL Joins and try our demo tutorial for a better understanding of SQL JOINS. You can download it from hereDownload Here

This is it for SQL JOINS tutorial we hope this gives you a better understanding of JOINS and helps you write more efficient sql queries.

All our fellow developers are free to comment us in case of any doubt our team is ready to help them,you can also mail us various topics on which you want tutorials.

So guys keep moving
Because hurdles aren’t really hurdles

5 Comments

  1. Peter Kokot said:

    The code example in this tutorial uses prehistorical ext/mysql with mysql_query() and other mysql_* function that won’t work on latest PHP 7 versions anymore. So instead of that either mysqli or PDO should be used… So, probably the code example files should be refreshed with PDO.

    Rating: 5.0. From 1 vote.
    Please wait...
    February 20, 2017
    Reply
    • Saurabh Dubey said:

      Yes, Peter we will update it soon and our future tutorial will be free from all these

      Thanks

      No votes yet.
      Please wait...
      February 20, 2017
      Reply
  2. Warren said:

    HI, Thanks for the amazing tutorials, they are always so helpful and relevant to real life situations. I have updated the above tutorial to mysqli. let me know if I should send you my updated version

    No votes yet.
    Please wait...
    February 25, 2017
    Reply
    • Saurabh Dubey said:

      Thank You warren we have also updated the tutorials now and will publish the updated one in future

      No votes yet.
      Please wait...
      February 25, 2017
      Reply
  3. Amit said:

    Your articles are always great and cover topics very smoothly. I enjoyed them lot

    No votes yet.
    Please wait...
    July 17, 2017
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve : *
5 + 27 =