MySQL JOIN Clauses (4/4) – ON and USING Clauses

Development

In this short article series I would like to describe the meaning of MySQL Join clauses. MySQL semantics is similar among different SQL database engines, so this knowledge could be proudly used in other SQL environments. Have a nice reading in part four and also the last one.

Here, in the part three, you can read about the Outer JOIN Clause. Now, let’s focus on the ON and USING Clauses.

In previous articles we saw several JOIN examples. There we have used ON clause to match the reocrds between tables.

USING Clause

This clause can also be used for the same purpose. The difference with USING to ON is that need to have identical column names for matched columns in both tables.

In the movies table so far we used id column as a primary key. We referred to the same values in the members table with the column named as movie_id.

To use the USING clause, w have to rename the movies table primary key from id to movie_id. We do this in order to have identical column names. To rename, let’s execute the following script:

ALTER TABLE `movies` CHANGE `id` `movie_id` INT(11) NOT NULL AUTO_INCREMENT;

Next, let’s execute the following script with the USING clause:

SELECT A.`title`, B.`first_name`, B.`last_name`
FROM `movies` as A
LEFT JOIN `members` as B
USING (`movie_id`)

Why should we use JOINS?

If you have some experience in database programming you know we can run queries one by one – of course in a production environment, but using JOINs, you can get the results much faster by using only a one query. It will also improve server’s performance.

Conclusion

JOINs allow us to combine data from more than one table into a single result set.
JOINs have better performance compared to sub queries.
INNER JOINS only return rows that meet the given criteria.
OUTER JOINS can also return rows where no matches have been found. The unmatched rows are returned with the NULL value.
The frequently used clause in JOIN operation is ON. USING clause requires the same columns name for both tables.
JOINS can also be used in other Clauses such as GROUP BY, WHERE, LIMIT, SUB QUERIES, AGGREGATE FUNCTIONS etc.

It is obvious, that we can achieve better MySQL and application performance by use of JOINs.