MariaDB Joins

MariaDB's ability to handle a variety of join types is one of its primary characteristics that makes it an effective tool for managing relational databases. Joins let you describe relationships between tables so you may access data from several tables. We'll go into the details of MariaDB joins in this post, looking at their kinds, syntax, and practical implementations.

MariaDB Joins

When we use the join keyword query gets executed and a matching record is shown from various tables that may be retrieved using MariaDB JOINS. Every time a SQL query joins two or more tables, a MariaDB JOIN is executed.

Let's look at how the MariaDB joins work by creating example tables and inserting data into them.

Create Tables

First, let's create an employee table.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
Hire_date DATE
);

Now let's create the another table named as worker.

CREATE TABLE worker (
    worker_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
Hire_date DATE
);

Insert data into both the tables.

Insert Data

INSERT INTO employees VALUES
(1, 'Maram', 'Doe', 101, 50000.00, '2022-01-15'),
(2, 'Vamshi', 'Smith', 102, 60000.00, '2022-02-20'),
(3, 'Sukumar', 'Johnson', 101, 55000.00, '2022-03-25');

INSERT INTO worker VALUES
(101, 'Minal', 'Williams', 'IT', 48000.00, '2022-04-10'),
(102, 'Vardhana', 'Brown', 'HR', 52000.00, '2022-05-15'),
(103, 'Kavya', 'Clark', 'IT', 49000.00, '2022-06-20');

Types of MariaDB Joins

1. Inner Join/ Simple Join

The INNER JOIN keyword specifies records with matching values in both tables.

Syntax:

SELECT col1, col2,.. FROM table1 INNER JOIN table2 ON table1.columns = table2.column;

• SELECT col1, col2,.. : This part select the columns you want to retrieve. You can add the multiple columns separated by commas.
  
• FROM table1 : This clause shows the table from which you want to select data.
  
• INNNER JOIN table2 : This part shows that you want to perform INNER JOIN with another table named table2. It returns only the rows that have matching values in both tables based on some specified conditions.

Example 1:

SELECT * FROM employee INNER JOIN worker ON employee.department_id = worker.worker_id;

This query will retrieve employees and the worker data names that belong to them using generic department_id/department

Output:

Example 2:

SELECT employee.employee_id, employee.first_name AS employee_first_name, worker.worker_id, worker.first_name AS worker_first_name FROM employee INNER JOIN worker ON employee.department_id = worker.worker_id;

This query will retrieve a list of workers and their corresponding employees using the shared department_id and worker_id.

Output:

2. Left Outer Join/ Left Join

Left Join retrieve all records from the left table and the corresponding facts from the right table.

Syntax:

SELECT col1, col2,.. FROM table1 LEFT JOIN table2 ON table1.columns = table2.column;

• SELECT col1, col2,.. : This part select the columns you want to retrieve. You can add the multiple columns separated by commas.
  
• FROM table1 : This clause shows the table from which you want to select data.
  
• LEFT JOIN table2 : This section indicates that you wish to use another table called table2 in an LEFT JOIN. The rows from Table 1 on the left and the corresponding rows from table 2 on the right will be returned.
  
• ON table1.columns = table2.column : This part defines the condition for the join.

Example 1:

SELECT employee.employee_id, employee.first_name AS employee_first_name, worker.worker_id, worker.first_name AS worker_first_name FROM employee LEFT JOIN worker ON employee.department_id = worker.worker_id WHERE employee.department_id = 101;

This query will obtain a list of every employee along with the worker information that belongs to them. Display NULL values for worker fields in the event that a match cannot be found in the worker table.

Output:

Example 2:

SELECT employee.employee_id, employee.first_name AS employee_first_name, worker.worker_id, worker.first_name AS worker_first_name FROM employee LEFT JOIN worker ON employee.department_id = worker.worker_id;

This query retrieves the employee_id and first_name from the employees table and the corresponding worker_id and first_name from the worker table where there is a match on the department_id and worker_id.

Output:

3. Right Outer Join/ Right Join

Right Join retrieve all records from the right table and the corresponding facts from the left table.

Syntax:

SELECT col1, col2,.. FROM table1 RIGHT JOIN table2 ON table1.columns = table2.column;

• SELECT col1, col2,.. : This part select the columns you want to retrieve. You can add the multiple columns separated by commas.
  
• FROM table1 : This clause shows the table from which you want to select data.
  
• RIGHT JOIN table2 : This part specifies that you want to perform RIGHT JOIN with another table named table2. It will return the rows from the right table(table2) and matching rows from the left table(table1).
  
• ON table1.columns = table2.column : This part defines the condition for the join.

Example 1:

SELECT employee.employee_id, employee.first_name AS employee_first_name, worker.worker_id, worker.first_name AS worker_first_name FROM employee RIGHT JOIN worker ON employee.department_id = worker.worker_id;

This query will obtain a list of every employee along with the worker information that pertain to them. Display NULL values for employee fields if there isn't a match in the employee table.

Output:

Example 2:

SELECT * FROM employee RIGHT JOIN worker ON employee.department_id = worker.worker_id;

Obtain a list of all employees along with the relevant worker information. Display NULL values for employee fields if there isn't a match in the employee table:

Output:

4. Cross Join

It returns the cartesian product of the two tables. It combines each row from the first table with every row from the second table.

Syntax:

SELECT col1, col2,... FROM table1 CROSS JOIN table2;

• SELECT col1, col2,.. : This part select the columns you want to retrieve. You can add the multiple columns separated by commas.
  
• FROM table1 : This clause shows the table from which you want to select data.
  
• CROSS JOIN table2 : This part specifies that you want to perform RIGHT JOIN with another table named table2. It will return the rows from the right table(table2) and matching rows from the left table(table1).

Example 1 :

SELECT employees.employee_id, employees.first_name AS employee_first_name, employees.department_id, worker.worker_id, worker.first_nam
AS worker_first_name, worker.department
FROM employees CROSS JOIN worker;

This query will retrieve a list of all possible combinations of employees and workers, regardless of any matching conditions.

Output:

Conclusion

The art of joining tables is important for anyone working with relational databases. MariaDB supports various join types. This gives developers powerful tools to retrieve and analyze data efficiently. The creation of sample tables, namely employees and worker, is demonstrated, followed by the insertion of data to set the stage for join operations. By following the available join types, their syntax, and best practices, you can harness the full potential of MariaDB. This helps in building high-performance database applications.