MySQL JOINS - Preparations

JOIN is a clause that is used to combine rows from two or more tables based on a related column between them.

When you have data stored across multiple tables in your database, you may want to retrieve data that involves information from both tables.

There are different types of JOINs in MySQL, including:

1. INNER JOIN: returns only the matched rows from both tables based on the common column.

2. LEFT JOIN: returns all the rows from the left table and only the matched rows from the right table.

3. RIGHT JOIN: returns all the rows from the right table and only the matched rows from the left table.

4. FULL OUTER JOIN (or simply OUTER JOIN): returns all the rows from both tables, including the non-matched ones.

Preparations

We will create database Corp:

mysql> CREATE DATABASE Corp;
Query OK, 1 row affected (0.20 sec)

mysql>

After creating the Corp database, you can use it to create tables, insert data, and perform other database-related operations. 

We will activate it:

mysql> USE Corp;
Database changed
mysql>

This will change the default database to Corp, and any subsequent commands will be executed in the context of that database.

Time for table Stores:

mysql> CREATE TABLE Stores (
    -> s_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> city VARCHAR (30),
    -> store_name VARCHAR (30),
    -> product VARCHAR (50),
    -> available INT,
    -> price INT);
Query OK, 0 rows affected (0.56 sec)

mysql>

This MySQL query creates a new table named Stores with six columns.

The NOT NULL constraint on the s_id column ensures that it's always populated with a value, and the PRIMARY KEY constraint specifies that it's the main identifier of the table.

The AUTO_INCREMENT option for s_id tells MySQL to automatically generate a unique value for this column for each new row added to the table.

Now, table Documents:

mysql> CREATE TABLE Documents (
    -> d_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> internal_number INT,
    -> s_id INT);
Query OK, 0 rows affected (0.70 sec)

mysql>

The absence of a REFERENCES clause in the s_id column definition means that it's not a strict foreign key, and it doesn't enforce any referential integrity constraints.

However, you can still use it as a reference to the s_id column of the Stores table to retrieve data from both tables together using JOIN operations.

Tables are there:

mysql> SHOW TABLES IN Corp;
+----------------+
| Tables_in_corp |
+----------------+
| documents      |
| stores         |
+----------------+
2 rows in set (0.00 sec)

mysql>

Structure of table Stores:

mysql> DESCRIBE stores;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| s_id       | int         | NO   | PRI | NULL    | auto_increment |
| city       | varchar(30) | YES  |     | NULL    |                |
| store_name | varchar(30) | YES  |     | NULL    |                |
| product    | varchar(50) | YES  |     | NULL    |                |
| available  | int         | YES  |     | NULL    |                |
| price      | int         | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql>

Structure of table Documents:

mysql> DESCRIBE documents;
+-----------------+------+------+-----+---------+----------------+
| Field           | Type | Null | Key | Default | Extra          |
+-----------------+------+------+-----+---------+----------------+
| d_id            | int  | NO   | PRI | NULL    | auto_increment |
| internal_number | int  | YES  |     | NULL    |                |
| s_id            | int  | YES  |     | NULL    |                |
+-----------------+------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

What we did in this tutorial is important preparation for tutorials on JOINs. Let's go.