In SQL, an INNER JOIN is utilized to merge rows from two or more tables based on a shared column. It only delivers the data from the intersection of the tables or the rows for which the stated condition is true. Here’s the basic syntax for using INNER JOIN in SQL:
INNER JOIN table2 ON table1.column = table2.column;
Let’s break down the syntax and provide an example:
- SELECT column_list: Replace this with the columns you want to retrieve from the joined tables.
- table1 and table2: These are the names of the tables you want to join.
- ON table1.column = table2.column: This specifies the condition for the join. It defines how the two tables are related. You need to replace table1.column and table2.column with the actual column names that relate to the tables.
Here’s a simple example using two hypothetical tables, “Customers” and “Orders“:
- Assume the “Customers” table has columns: customer_id, customer_name, and email.
- Assume the “Orders” table has columns: order_id, customer_id, order_date, and total_amount.
You can write an INNER JOIN query to retrieve the customer name and order date for all orders:
SELECT Customers.customer_name, Orders.order_date
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;
- In this illustration, the matching customer_id field is used to combine rows from the “Customers” and “Orders” tables. It only returns rows where the customer_id values are present in both tables.
- The INNER JOIN must have the same data type or compatible data types in both tables for the column(s) used in the ON condition to function properly.
- The use of more than two tables is also possible with INNER JOINS. As more INNER JOIN clauses and ON conditions are required, simply keep expanding the syntax.
- Remember that INNER JOINS only returns the rows from both tables whose values match. Depending on your individual needs, you might think about using other joins, such as LEFT JOIN to retrieve all the records from the left (first) table and matching records from the right, RIGHT JOIN to retrieve all the records from the right table, and matching records from left, or FULL JOIN to fetch all the records from both table.