MYSQL- Joining Tables with Inner Join statement

MYSQL is a widely used relational database management system (RDMS). It is an open-source application.

A “Join statement” is used to combine rows from two or more tables, based on a related column between them.

👉INNER JOIN -Returns records that have matching values in both tables.

✅Generating Random Data for the scheme. Creating EMPLOYEE and EMPLOYEE DEPARTMENT Tables and inserting values by using a few MYSQL Commands.

🔷Using MYSQL Commands and Data types for creating an Employee Table.

1. CREATE DATABASE — creates a new database.
2. CREATE TABLE- creates a new table.
3. USE — selects a simple database and then performs operations on it using the in-built commands.
4. INSERT INTO — inserts new data into a database.
5. INT — Integer numerical (no decimal).
6. VARCHAR — data type stores variable-length character strings.
7. INNER JOIN — selects records that have matching values in both tables.
8. REFERENCE keyword — Is used to define which table and column is used in a foreign key relationship
9. AUTO INCREMENT — allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
10. NOT NULL — If you do not want a column to have a NULL value, then you need to define such a constraint on the column specifying that NULL is now not allowed for a specific column.
11. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
12. A PRIMARY KEY is a field in a table that uniquely identifies each row/record in a database table.

✅STEPS TO FOLLOW:

🟡Creating an Employees Department Database.

Database Employees Department

Output:

Output — Database Employees Department

🟡Creating an Employee Department Table.

Employee Department Table

Output:

Output — Employee Department Table

🟡Creating an Employee Table.

Employee Table

Output — Employee Table

🟡Inserting Employee Department values.

Created Random Values for Employees Department by using the INSERT INTO command.

Employee Department values

Output — Employee Department values

🟡Inserting Employee values.

Created Dummy Values for Employee by using the INSERT INTO command.

Employee values

Insert more values for Employee Table as per the match value column count.

🟡INNER JOIN is the most common form of JOIN and is very widely used.

Let us see the syntax:

Inner Join Statement

So in this case, we would refer to the Department by a field Department_id in the EMPLOYEE table and all relevant information related to Department — like Department Info, Department head, etc. May be kept as part of the EMPLOYEE_DEPARTMENT table.

So in a nutshell — EMPLOYEE and EMPLOYEE_DEPARTMENT are linked to each other through the Department_id field, which is acting as a FOREIGN KEY for the EMPLOYEE table and PRIMARY KEY for the EMPLOYEE_DEPARTMENT table.

Output for the above query

Output- Inner Join

All 20 rows can be seen after inserting the Inner Join statement.