Inner Join In Sql database
In SQL database management systems, INNER JOIN is one of the most frequently used join types and a popular table join method. The INNER JOIN table method is used to relate or connect two or more database table rows based on a related table column between them. Specifically, the INNER JOIN joining method retrieves and displays only those table rows where the two different database tables have a match in the specified columns.
Syntax of INNER JOIN in SQL database management.
SELECT columns
FROM tableX
INNER JOIN tableY
ON tableX.column_name = tableY.column_name;
Elements of INNER JOIN in SQL database management.
- tableX and tableY – These are the names of the two different database tables being joined.
- column_name – This is the column in each database table on which the database user applies the join process. These table columns typically have matching values. For example, a foreign key in one database table and a primary key in another table can be joined and interconnected if they have the same values.
- columns – These are the specific table columns in the existing database tables that the database user wants to retrieve from the tables.
The result of applying the INNER JOIN process method to two different database tables is a new result set containing only those table rows where a match was found between the two database tables.
How INNER JOIN works in SQL database management.
In SQL database management, the INNER JOIN joining table method retrieves and displays those table column data rows where the values in the two different database tables match. If there is no proper match for a specific table row in either database table, that table row is removed from the final result set in the INNER JOIN method.
Example of a simple INNER JOIN in SQL database management.
Database tables.
- Employee – This includes displaying basic information about the employee table in the employee database table.
- employ_id, emp_name, department_id, dept_location
- Department – This includes displaying basic information about the department in the department table.
- department_id, department_name, email
Basic output data of the employee and department tables.
Employe table output.
employe_id emp_name department_id dept_location
101 Siddhi deora S101 Delhi
201 Harry deora H204 Mumbai
301 Bhavshi deora B302 Chennai
Department table output.
department_id department_name email
K403 Marketing abc@domainame.com
M908 Development xyz@domainame.com
N888 Design pqr@domainame.com
Database table query statement.
SELECT employe.emp_name, department.department_name, department.department_email
FROM employe
INNER JOIN department
ON employe.department_id = department.department_id;
Explanation of the database table query join statement.
- Here, the query connects both the employee and department database tables by matching the department_id from both tables using the proper key, and displays the record rows from both table queries.
- The database query output retrieves and displays the employee’s name and the department_name and email related to the employee from the table columns.
Result of the inner join of both the employe and department tables.
emp_name department_name email
Siddhi deora Marketing abc@domainame.com
Harry deora Development xyz@domainame.com
Bhavshi deora Design pqr@domainame.com
How INNER JOIN works in SQL database tables.
This query in the database table finds those employees whose department_id properly matches the department_id in the department table. All database tables have a related employee department, therefore all table rows are included in the result set.
Example of an INNER JOIN with no matching rows.
Let’s modify the employee table so that it includes an employee who does not belong to any department.
Modified updated employee table.
employee_id emp_name department_id dept_location
101 Siddhi deora S101 Delhi
201 Harry deora H204 Mumbai
301 Bhavshi deora B302 Chennai
701 Kunal Verma K901 Gujarat
Database table query.
SELECT employe.name, department.department_name, department.department_email
FROM employe
INNER JOIN department
ON employe.department_id = department.department_id;
Table query result.
emp_name department_name email
Siddhi Deora Marketing abc@domainame.com
Harry Deora Development xyz@domainame.com
Bhavshi Deora Design pqr@domainame.com
Amit Jain Null
Database query explanation.
- Here, the value of department_id for the employee Amit Jain is defined as NULL in the employee table, therefore, there is no matching department for him.
- The INNER JOIN excludes Amit from the result because there is no related row available for NULL in the department table; a match is required.
INNER JOIN example with multiple tables in a database.
Database users can also join two or more database tables using the INNER JOIN joining method. For example, consider creating and analyzing another third table, ‘pay’, which contains employee salary information.
Database table names and query.
- employee – Here, the employee table has table rows/columns such as employe_id, emp_name, department_id, etc.
- department – Here, the department table has table rows such as department_id, department_name, etc.
- pay – Here, the pay table has table rows/information such as employe_id, salary, etc.
Multiple database table query.
SELECT employe.emp_name, department.department_name, pay.salary
FROM employe
INNER JOIN department
ON employe.department_id = department.department_id
INNER JOIN pay
ON employe.employe_id = pay.employe_id;
Explanation of the multiple database table query.
- The database table query given above connects three different database tables: employee, department, and pay.
- It extracts the employee’s name, employee’s department name, and their salary information from the employee table, where matching rows exist in both the department and pay tables.
Result of employe, department, and pay table matching.
emp_name department_name pay
Siddhi deora Marketing 99999
Harry deora Development 87342
Bhavshi deora Design 34432
How INNER JOIN works in SQL databases.
In SQL databases, the query first matches the employee to the department based on the department_id, and then properly matches the table rows to the salary based on the employe_id.
Only those employee records are displayed that have both department and employee pay salary records stored.
Using INNER JOIN with aliases in SQL databases.
Database users can also use table alias features to easily create and make table queries more readable.
SQL database query statement with aliases.
SELECT e.emp_name, d.department_name
FROM employe AS e
INNER JOIN department AS d
ON e.department_id = d.department_id;
Explanation of the query with aliases.
- Here in this example, e and d are commonly used alias characters for the employee and department tables.
- Using aliases can help make database queries more descriptive, especially in database queries that involve multiple joins or long, cumbersome table names.
Conclusion of Inner Join in SQL database.
- In SQL database management systems, INNER JOIN is used to extract data from multiple database tables simultaneously, where it properly matches with the pre-defined table columns. This is one of the multiple SQL table join operations that helps database users, and it is mostly used for extracting database queries from multiple tables, creating relationships and links between multiple different database tables.
- The INNER JOIN command statement in database tables only displays rows that have exact matching values in both inner join tables. This is the most commonly used and popular join method or process in SQL database management software.
- Database users can use the INNER JOIN method with two or more database tables at a time, and they can also use table aliases to make their database queries more descriptive and readable.
