How to perform an INNER JOIN on multiple columns

Aliases: a coder's best friend

Aliases, in this context, are like pet names but for tables – they make things more personal and easier. Use them to make your SQL code easier to read and maintain, especially when dealing with tables that have similar column names.

SELECT A.*, B.*
FROM TableA AS A
INNER JOIN TableB AS B
ON A.id = B.linked_id AND A.name = B.name;


Those aliases (A for TableA and B for TableB) help avoid confusion between which columns belong to which table. Think of it as giving them a VIP pass, making them easier to spot in the crowd.

Dealing with complex joins

When joining tables based on multiple conditions, parent the conditions with parentheses to ensure the logical structure of your query is clear. Remember, order of operations isn't only important in math.

SELECT A.*, B.*
FROM TableA AS A
INNER JOIN TableB AS B
ON (A.id = B.id) AND (A.start_city = B.end_city AND A.start_code = B.end_code);


Here throughout Sunday with a triple join occurrence! Get your popcorn, we're matching id, and where start city to end city and start code to end code align.

Switching up your join style

At times, be prepared to pivot from INNER JOIN. If you require a more expansive data set, a LEFT JOIN may serve better due to its inclusive nature.

SELECT A.name, B.city
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.id = B.matching_id AND A.code = B.code;


LEFTy here, bringing you all the records from the left table and matched records from the right one. The unsynced right table records end up going solo as NULL.

Joining the same table...twice!

Yes, you heard that right. For complex data retrievals like linking departure and arrival data, you may need to join the same table twice. Talk about self-love.

SELECT flights.*, dep_city.name AS DepartureCity, arr_city.name AS ArrivalCity
FROM flights
INNER JOIN airports AS dep_city ON flights.dep_code = dep_city.code
INNER JOIN airports AS arr_city ON flights.arr_code = arr_city.code;


Are we seeing double? Nope, that's just us joining the flights table with the airports table twice — once for matching departure codes and once for arrival codes. Dual citizenship in table-land!

Database system specifics

The SQL dialect you're using comes with its own quirks. MySQL, MS SQL Server, Oracle, and PostgreSQL all adhere to the SQL standard, but they might have unique syntax or limitations. Remember, 'when in Rome, do as the Romans do' — adapt to the system you're working on.

WHERE: The SQL filter

To narrow down your results, incorporate a WHERE clause. This allows you to sift down your results to meet the required criteria. It's like being the Sherlock of SQL.

SELECT *
FROM employees
INNER JOIN departments
ON employees.dep_id = departments.id
WHERE departments.name = 'Engineering';


Cue dramatic music Only showing employees who decided to embrace circuits over serenity and chose the Engineering department.