MySQL JOIN Function Differences (MJFD)

Substance:

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. INNER JOIN
4. STRAIGHT_JOIN
5. NATURAL JOIN
6. CROSS JOIN

Get to know the join function in MySQL

1. LEFT OUTER JOIN

The OUTER keyword is actually optional so, whether it is written or not, the result will still be the same, because basically, LEFT OUTER JOIN = LEFT JOIN, while LEFT JOIN itself is part of OUTER JOIN. Illustration:

Assuming that all fields in the table above are already filled with data.

Information:

Basically, all fields in the table above already have their own data, but because we use the LEFT OUTER JOIN query command, the data displayed is only the field on the left side (the same table scope), while the field data on the right side (the same table scope) is hidden / not displayed, in the table above we indicate it in blue.

Example:

By using the Northwind database we will combine 2 table relationships, such as customers and orders.

If you want to understand further by practicing it, please download the Northwind database first  HERE .

With the Query command as below:

SELECT customers.CustomerID, customers.ContactName,
orders.OrderID, orders.OrderDate
FROM customers LEFT OUTER JOIN orders
ON customers.CustomerID=orders.OrderID;

or

SELECT customers.CustomerID, customers.ContactName,
orders.OrderID, orders.OrderDate
FROM customers LEFT JOIN orders
ON customers.CustomerID=orders.OrderID;

Then you will get the results as shown below.

2. RIGHT OUTER JOIN

Identical to LEFT OUTER JOIN, which is both part of OUTER JOIN, only RIGHT OUTER JOIN is the opposite, namely RIGHT OUTER JOIN = RIGHT JOIN, where the data field to be displayed is on the right side, with the note that it is still within the scope of the same table. Illustration:

Assuming that all fields in the table above already have data.

Information:

Basically, all fields in the table above already have their own data, but because we use the RIGHT OUTER JOIN query command, the data displayed is only the fields on the right side (the same table scope), while the field data on the left side (the same table scope) is hidden / not displayed, in the table above we indicate it in blue.

Example:

By using the Northwind database we will combine 2 table relationships, such as customers and orders.

With the Query command as below:

SELECT customers.CustomerID, customers.ContactName,
orders.OrderID, orders.OrderDate
FROM customers RIGHT OUTER JOIN orders
ON customers.CustomerID=orders.OrderID;

or

SELECT customers.CustomerID, customers.ContactName,
orders.OrderID, orders.OrderDate
FROM customers RIGHT JOIN orders
ON customers.CustomerID=orders.OrderID;

Then you will get the results as shown below.

3. INNER JOIN

With INNER JOIN, the tables will be combined in both directions, so that there is no NULL data on one side or in other words, NULL data will not be displayed. For example, by using the northwind database we will combine the customers and orders tables where we will display a list of customers who have made orders.

By using the Query command as follows.

SELECT customers.CustomerID, customers.Country, orders.OrderID, orders.OrderDate
FROM customers INNER JOIN orders
ON customers.CustomerID = orders.CustomerID

Then the results obtained:

Conclusion:

INNER JOIN is used to search for the same data between two tables, while OUTER JOIN is used to search for the same data as well as data that is not the same.

4. STRAIGHT_JOIN

From the word straight which means straight or honest or as is, so STRAIGHT_JOIN is a MySQL operator that is used to combine 2 tables completely and as is, even though there is no data relationship.

The STRAIGHT_JOIN command is the same as JOIN, but STRAIGHT_JOIN does not recognize the WHERE clause. Using the northwind database, consider the following usage example.

SELECT * FROM customers
STRAIGHT_JOIN products

Even though there is no relationship (similarity of data) between the customers table and the products table, the two can still be combined with the STRAIGHT_JOIN command.

Execution result:

5. NATURAL JOIN

The natural join operation is an equijoin operation that has equality in all fields that have the same name in table R and table S. In this case, we can omit the condition in the join operation because it will produce two fields with the same name.

So to perform a NATURAL JOIN between two tables, the following requirements must be met: The two tables to be NATURAL JOINed must have at least one column or field name that is the same and the same data type, otherwise an error will occur.

Example:

By using the norhwind database, we will perform a NATURAL JOIN on the customers table with orders, where both tables have the same fields and data types in the CustomersID column.

SELECT CustomerID, CompanyName, OrderID, OrderDate
FROM customers NATURAL JOIN orders

And the result is as below.

6. CROSS JOIN

Cross Join is also called CARTESIAN PRODUCT, which is the multiplication of 2 tables.

Illustration:

For example, data in table A: {a,b,c,d}, data in table B: {F,G,H,I}

If table A CROSS JOIN table B, it will be {aF, aG, aH, aI, bF, bG, bH, bI, cF, cG, cH, cI, dF, dG, dH, dI}.

Example:

With the northwind database, we will do a CROSS JOIN on the categories table with the shippers table.

Where the categories table is as follows.

And the shippers table is as follows.

If both are CROSS JOIN,

SELECT CategoryName, CompanyName
FROM categories CROSS JOIN shippers

Then the result will be as below.

That's the discussion about the differences in JOIN functions in MySQL this time, hopefully it is useful, thank you... greetings programmer.

Netizens

Q1: It really helps me a lot in doing my assignment, thank God. Thank you. 

A1: You're welcome Bagusyz.. good luck!

Q2: Great bro, very useful. Thank God, it's enough for additional lecture material. Thank you bro. Before that, let me introduce myself. My name is Abdurrahman Fadhillah. I am currently studying at ISB Atma Luhur, Pangkalpinang City, Babel. Don't forget to visit the website, bro.

A1: Hi Abdurrahman Fadhillah, greetings young scholar.. keep up the spirit of learning, CONTINUE YOUR STRUGGLE!

How to Join MySQL Tables

Join is an operation used to obtain combined data from two or more tables. In SQL Server there are three types of joins, namely:

How to Join Between Tables Using SQL

1. Cross Join (cartesian)

Produces a combination of all rows contained in a table, both paired and unpaired. Although this join is never used, Cross Join is the basis of inter-table joins.

2. Inner Join

This join only produces output in the form of a combination of paired rows, other rows will be eliminated and other rows that do not have a pair will also be removed.

3. Outer Join

Almost the same as Inner Join, the difference is that rows that do not have a pair will also be processed. Outer Join is still divided into three more:

• Left Outer Join
• Right Outer Join
• Full Outer Join

4. Cross Join

The basic commands:

SELECT * FROM tabel1, tabel2, tabel3, ...

or

SELECT * FROM tabel1.field1, tabel2.field1, tabel3.field1, ...

example:

select * from nasabah, cabang_bank

select * from nasabah.idnasabah, nasabah.nama_nasabah,
cabang_bank.nama_cabang from nasabah, cabang_bank

5. Inner Join

Below are some examples of Inner Join, try them on Query Analyzer and observe the results. If necessary, develop the commands.

Select * from nasabah, nasabah_rek
where nasabah.idnasabah = nasabah_rek.idnasabah

select * from rekening, nasabah_rek
where rekening.norek = nasabah.norek

select a.nama_nasabah, b.kode_cabang, c.norek
from nasabah a, rekening b, nasabah_rek c
where a.idnasabah = c.idnasabah and b.norek = c.norek

select * from nasabah a inner join nasabah_rek b
on a.idnasabah = b.idnasabah

select * from rekening a join nasabah_rek b on a.norek = b.norek

select a.nama_nasabah, b.kode_cabang, c.norek
from nasabah a join nasabah_rek c
on a.idnasabah = c.idnasabah join rekening b on b.norek = c.norek

6. Outer Join

The following is an example of using Left Outer Join:

select * from nasabah left join nasabah_rek
on nasabah.idnasabah = nasabah_Rek.idnasabah

select a.nama_nasabah, b.kode_cabang, c.norek
from nasabah a left join nasabah_rek c
on a.idnasabah = c.idnasabah left join rekening b
on b.norek = c.norek

Here is an example of using Right Outer Join:

select * from nasabah right join nasabah_rek
on nasabah.idnasabah = nasabah_rek.idnasabah

Here is an example of using Full Outer Join:

select * nasabah full join nasabah_rek
On nasabah.idnasabah = nasabah_rek.idnasabah

Here is an example of a command to see the difference between Inner Join and Outer Join, for example:

select a.norek, b.nama_cabang
from rekening a inner join cabang_bank b
on a.kode_cabang = b.Kode_cabang

Another example:

select a.norek, b.nama_cabang from rekening a
left outer join cabang_bank b on a.kode_cabang = b.kode.cabang

Select a.norek, b.nama_cabang from rekening a
right outer join cabang_bank b on a.kode_cabang = b.kode.cabang

Select a.norek, b.nama_cabang from rekening a
full outer join cabang_bank b on a.kode_cabang = b.kode.cabang

Simply put, it can be concluded that Cross Join is a type of join that does not use requirements. While other types of joins require requirements. The requirements referred to here are that the data in the FK column (relationship key) in the transaction table must be the same as the data in the PK column (primary key) in the master table.

The requirements can be written as follows or vice versa:

TabelMaster.KunciPrimary = TabelTransaksi.KunciForeign

Therefore, the requirement of FK and PK that are related to each other, then Inner Join and Outer Join can only be done on tables that have a relationship. While Cross Join can be done on any/all tables.

TASK

Before receiving an assignment from the lecturer, study Chapter 10 Joining Between Tables above once again, to make it easier to understand and accept the assignment given.

Book Table Name

| Nama Field | Tipe Data | Lebar | Keterangan               |
|------------|-----------|-------|--------------------------|
| Idbuku     | Varchar   | 2     | Nomor identitas buku     |
| Idterbit   | Varchar   | 2     | Nomor identitas penerbit |
| Judul      | Varchar   | 100   | Judul buku               |
| Penulis    | Varchar   | 30    | Nama penulis buku        |
| Harga      | Integer   | 6     | Harga satuan buku        |

Publisher Table Name

| Nama Field | Tipe Data | Lebar | Keterangan               |
|------------|-----------|-------|--------------------------|
| Idterbit   | Varchar   | 2     | Nomor identitas penerbit |
| Namaterbit | Varchar   | 50    | nama penerbit            |
| Alamat     | Varchar   | 100   | Alamat penerbit          |

Sell ​​Table Name

| Nama Field | Tipe Data | Lebar | Keterangan               |
|------------|-----------|-------|--------------------------|
| Idbuku     | Varchar   | 2     | Nomor identitas buku     |
| Nota       | Varchar   | 5     | Nomor nota penjualan     |
| Tgljual    | Date      | 8     | Tanggal penjualan buku   |
| Qty        | Integer   | 2     | Jumlah buku yang terjual |