CONNECT in sql

Cross JOIN

Cross JOIN is the simplest form of JOIN and matches each row from one database table with all rows in another.

In other words, this gives us combinations of every row in the first table with all the records in the second table.

Suppose we want to get all records of all movies, we can use the script shown below to get the desired results.

SELECT * FROM `movies` CROSS JOIN `members`

Running the above script in MySQL Workbench gives us the following results.

INNER JOIN

An inner JOIN is used to return rows from both tables that satisfy a given condition.

Let's say you want to get a list of members who have rented movies along with the titles of the movies rented from them. You can just use an INNER JOIN for this, which returns rows from both tables that satisfy the given conditions.

SELECT members.`first_name` , members.`last_name` , movies.`title`
FROM members ,movies
WHERE movies.`id` = members.`movie_id`

Running the above script gives

Please note that the above results script can also be written as follows to achieve the same results.

SELECT A.`first_name` , A.`last_name` , B.`title`
FROM `members`AS A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

EXTERNAL CONNECTIONS

MySQL outer joins return all records that match both tables.

It can detect records that do not match in the joined table. It returns NULL values for the records of the joined table if no match is found.

Sounds confusing? Let's look at an example −

LEFT JOIN

Let's now assume that you want to get the titles of all the movies along with the names of the members who rented them. It is clear that no one made some films. We can just use LEFT JOIN for this purpose.

LEFT JOIN returns all rows from the table on the left, even if no matching rows are found in the table on the right. If no match is found in the table on the right, NULL is returned.

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
ON B.`movie_id` = A.`id`

Running the above script in the MySQL workbench gives. You can see that in the returned result below, for movies that are not rented, the element name fields are NULL. This means that no suitable members have been found for this movie.

CORRECT CONNECTION

RIGHT JOIN is obviously the opposite of LEFT JOIN. RIGHT JOIN returns all columns from the table on the right, even if no matching rows are found in the table on the left. If no match is found in the table on the left, NULL is returned.

For our example, let's assume that you want to get the names of the members and the movies they rented. We now have a new member who hasn't rented any movies yet

SELECT A.`first_name` , A.`last_name`, B.`title`
FROM `members` AS A
RIGHT JOIN `movies` AS B
ON B.`id` = A.`movie_id`

Running the above script in MySQL Workbench gives the following results.

"ON" and "USING" positions

In the JOIN query examples above, we have used the ON clause to match records between tables.

The USING clause can also be used for the same purpose. The difference with USING is that both tables must have the same names for the matching columns.

So far in the "movies" table, we have used its primary key named "id". We referenced the same in the "members" table named "movie_id".

Let's rename the "id" field of the "movies" table to the name "movie_id". We do this in order to have identical matching field names.

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

Next, let's use USING with the LEFT JOIN example above.

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

Besides using ON and USING with JOIN, 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 might be wondering why we use JOIN when we can do the same task by running queries. Especially if you have some database programming experience, you know that we can run queries one by one, use the output of each in successive queries. Of course it is possible. But using JOIN you can get the job done with just one query with any search parameters. On the other hand, MySQL can achieve better performance with JOINs since it can use indexing. Simply using a single JOIN query instead of running multiple queries reduces the load on the server.Instead, multiple queries are used, resulting in more data transfer between MySQL and applications (software). Further, this requires more data manipulation at the end of the application.

It is clear that we can achieve better MySQL and application performance with JOIN.

Summary

• JOINS allow us to join data from more than one table into one result set.
• JOINS have better performance compared to subqueries
• INNER JOINS return only those rows that match the given criteria.
• OUTER JOINS can also return rows where no match is found. Non-matching rows are returned with the NULL keyword.
• The main types of JOIN include Inner, Left Outer, Right Outer, Cross JOINS, etc.
• JOIN operations often use the "ON" clause. The "USING" clause requires matching columns to have the same name.
• JOINS can also be used in other sections like GROUP BY, WHERE, SUB queries, aggregate functions, etc.