Comparing Tables and Columns in MySQL

In MySQL, we can compare tables and columns at the record level by looking at their values side by side. We will use MySQL JOIN clauses to do this. In this article, we look at the different JOIN operators through various examples.

As programmers, when building an application, we often find problems or conditions that require us to compare records in the database table. On this occasion, we will discuss the query of how to compare tables in MySQL tables and columns.

When comparing and displaying data stored from a database, we will often take data from several tables to be processed so that it becomes a single output. This makes the comparison easier and more efficient.

In most RDBMS including MySQL, this form of data retrieval can be done easily through established table relationships. This form of the relationship varies: we can have a one-to-one relationship, one-to-many, and many-to-many.

Compare 2 Tables in MySQL – Examples

Before going any further, we need to prepare a table that we will use in this article.

In this example, we use four tables, namely: customer table, product table, purchase table, and detail_transaction table. The structure and relationship of the four tables are as follows:

customer table

product table

transaction table

transaction_detail table

The relationships between the tables are described as follows:

Image 1: Relations Between Tables in The Sales Database

Image 2: Data Samples of Product Table

Image 3: Data Samples of Costumer Table

Image 4: Data Samples of Purchase Table

Comparing 2 Tables in MySQL Using the JOIN Statement

To combine and compare 2 tables in MySQL, we use the JOIN statement. In this case, we use several types of JOIN, namely INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN. The writing format is as follows:

1. SELECT table_name.column_name
3. FROM table_name
5. INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN table_name ON condition

In addition to using the ON statement to define conditions, we can use the USING statement, the writing format is as follows:

1. SELECT table_name.column_name
3. FROM table_name
5. INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN USING table_name (column_name)

This USING statement will use column names in parentheses to connect the two tables. This column must be in the table that you want to connect to and must have the same name.

Usually, related columns are defined as Primary Keys and Foreign Keys. This is part of database design best practice, but not a hard requirement.

Example 1: INNER JOIN Statement

The first way to combine tables is to use the INNER JOIN. With INNER JOIN, the table will be combined based on the same data, which is in both tables. If it is drawn in the form of a Venn diagram, the INNER JOIN form is shown in the following figure:

Image 5: Venn Diagram of INNER JOIN

In MySQL, writing an INNER JOIN can be done using the INNER JOIN clause, or just a JOIN clause. For example, we will display the data of the customer who made the purchase orders, the query that we run:

1. SELECT customer.id_customer,customer.name,purchase.date_purchase, purchase.total_purchase
3. FROM customer
5. INNER JOIN purchase ON purchase.id_customer = customer.id_customer;

The results will appear as below :

Image 6: The Example Result Of Run INNER JOINT Query

Example 2: OUTER JOIN Statement

The second way to combine tables in MySQL is to use the OUTER JOIN. In the OUTER  JOIN, all data in one table will be displayed, while the data in another table will only be displayed if the data is in the first table.

In MySQL, the OUTER JOIN has two types, namely the LEFT OUTER JOIN and RIGHT OUTER JOIN. In LEFT OUTER JOIN, all data in the left table will be displayed, while the data in the table on the right will only be displayed if the related data in the table appears in the table on the left.

If in the form of a Venn diagram, the LEFT OUTER JOIN form looks like the following example:

Image 7: Venn Diagram of LEFT OUTER JOIN

Example of using LEFT OUTER JOIN:

1. SELECT customer.id_customer,customer.name,purchase.date_purchase, purchase.total_purchase
3. FROM customer
5. LEFT OUTER JOIN purchase ON purchase.id_customer = customer.id_customer;

The results will appear as below:

Image 8: The Example Result Of Run LEFT OUTER JOIN Query

In the example above, we can see that with LEFT OUTER JOIN, the data on the left table (customer table) will all be displayed, while the data on the right table (purchase table) will only be displayed if the id_customer column values ​​appear in the id_costumer in the customer table.

Using JOIN statements allows us to compare the record values for each table side-by-side.