What are JOINS?
Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys.
Cross JOIN
Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another.
In other words it gives us combinations of each row of first table with all records in second table.
Suppose we want to get all member records against all the movie records, we can use the script shown below to get our desired results.
SELECT * FROM `movies` CROSS JOIN `members`
INNER JOIN
The inner JOIN is used to return rows from both tables that satisfy the given condition.
Suppose , you want to get list of members who have rented movies together with titles of movies rented by them. You can simply use an INNER JOIN for that, which returns rows from both tables that satisfy with given conditions.
SELECT members.`first_name` , members.`last_name` , movies.`title`FROM members ,moviesWHERE movies.`id` = members.`movie_id`
LEFT JOIN
Assume now you want to get titles of all movies together with names of members who have rented them. It is clear that some movies have not being rented by any one. We can simply use LEFT JOIN for the purpose.
The LEFT JOIN returns all the rows from the table on the left even if no matching rows have been found in the table on the right. Where no matches have been found in the table on the right, NULL is returned.
SELECT A.`title` , B.`first_name` , B.`last_name`FROM `movies` AS ALEFT JOIN `members` AS BON B.`movie_id` = A.`id`
RIGHT JOIN
RIGHT JOIN is obviously the opposite of LEFT JOIN. The RIGHT JOIN returns all the columns from the table on the right even if no matching rows have been found in the table on the left. Where no matches have been found in the table on the left, NULL is returned.
In our example, let's assume that you need to get names of members and movies rented by them. Now we have a new member who has not rented any movie yet
SELECT A.`first_name` , A.`last_name` , B.`title`FROM `members` AS ARIGHT JOIN `movies` AS BON B.`id` = A.`movie_id`
"ON" and "USING" clauses
In above JOIN query examples, we have used ON clause to match the records between table.
USING clause can also be used for the same purpose. The difference with USING is it needs to have identical names for matched columns in both tables.
In "movies" table so far we used its primary key with the name "id". We referred to same in "members" table with the name "movie_id".
Let's rename "movies" tables "id" field to have the name "movie_id". We do this in order to have identical matched field names.
ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;
Next let's use USING with above LEFT JOIN example.
SELECT A.`title` , B.`first_name` , B.`last_name`FROM `movies` AS ALEFT JOIN `members` AS BUSING ( `movie_id` )Apart from using ON and USING with JOINs you can use many other MySQL clauses like GROUP BY, WHERE and even functions like SUM, AVG, etc.
Why should we use joins?
Now you may think, why we use JOINs when we can do the same task running queries. Especially if you have some experience in database programming you know we can run queries one by one, use output of each in successive queries. Of course, that is possible. But using JOINs, you can get the work done by using only a one query with any search parameters. On the other hand MySQL can achieve better performance with JOINs as it can use Indexing. Simply use of single JOIN query instead running multiple queries do reduce server overhead. Using multiple queries instead that leads more data transfers between MySQL and applications (software). Further it requires more data manipulations in application end also.
No comments:
Post a Comment