This is the most commonly used type of join. It returns only the rows that have matching values in both tables. The syntax for an inner join is:
SELECT column1, column2
FROM table1
JOIN table2
ON table1.column = table2.column;
Real-life example: A retail store has a table of customers and a table of orders. To get a list of all orders along with the customer’s name and address, you could use an inner join to combine the data from the customers and orders tables.
SELECT customers.name, customers.address, orders.order_id, orders.purchase_date
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
There are several best practices for using inner joins in SQL Server:
Use the ON clause to specify the join condition: The ON clause should be used to specify the condition for the join, rather than using the WHERE clause. This makes the query more readable and ensures that the join is only applied to the specified columns.
Use indexes on join columns: To improve query performance, it is important to have indexes on the columns that are used in the join condition. This allows the database engine to quickly locate the matching rows in both tables.
Use aliases for table names: Using aliases for table names can make the query more readable and make it easier to reference the correct table when there are multiple tables in the query.
Use the INNER JOIN keyword only when necessary: When joining tables, it is best to be explicit and use the INNER JOIN keyword only when it is needed. Because it’s the default join type, it’s not needed to be specified
Be mindful of the order of the tables: The order of the tables in the join can affect the performance of the query. It’s best to put the table with the smallest number of rows first in the join.
Watch out for Cartesian products: When joining tables without specifying a join condition or specifying a non-restrictive condition, it can result in a Cartesian product, where every row from one table is combined with every row of the other table, resulting in a very large number of rows.
Use Explicit Columns: When joining tables, it’s best to be explicit and only select the columns that you need. This can help to reduce the amount of data that needs to be retrieved and processed, which can improve query performance.
Example:
SELECT customers.name, orders.order_id
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
In this example, the join is done on the customer_id column, and only the name and order_id columns are selected. This improves the readability and performance of the query.
