Database Joins are important to master. As you progress from a beginner to advanced beginner, you’ll soon need to combine data from more than one table. To do this, you’ll one of several SQL join types. In this series of articles I’ll show you how to write a query that combines, or joins, data from more than one table. By going through the examples, you appreciate the problem and understand the basic join syntax.This first article introduces the concept of joining tables. Its focus is on SQL join types, not their syntax.In my prior articles you learned about the need to normalize to make it easier to maintain the data. Though this makes it easier to maintain and update the data, it makes it very inconvenient to view and report information.Typically you need to cross reference, that is join, several tables to get the information you need!

You may be wondering what makes up a join condition. In many cases a join condition is just matching one or more fields in one table to those in another.Its fancy name is equi-join. Why? Because of the equals sign…No so fancy after all!Joins aren’t limited to exact matches, you’ll see in later articles where it is useful to use other comparison operators such as the greater than sign.A common join pattern used to join the primary key of one table to its foreign key. I suppose this is self-evident, but I think it is important to highlight it. Can you think why this is the case?

What process do we use to break up our data?

If you guessed normalization, you are correct. Through that process we break up dependencies within tables to eliminate update anomalies among other things, but in order to keep relationships, we introduce foreign keys.Let’s take an example from the sample database. Consider the following data model involving the Employees and Orders table. In this model each employee can place zero or more orders.

You are watching: What is the most common type of join?

The EmployeeID is the primary key in the Employees table and foreign key in the Orders table. For each employee there can be none, one, or perhaps many orders.Here is a list of all the employees. To keep it simple I’m only showing the LastName.
You can see this data using this select statementSELECT OrderID, EmployeeID, ShippedDateFROM OrdersTo create a report of employee LastName and the ShippedDate of the orders they placed, we need to combine information from both tables. To do we would create a join condition between the two tables on EmployeeID.When we work with select statements involving more than one table, we need a way to keep really clear which field is from which table. To do this the columns is qualified by the table name. The format is:tableName.ColumnnUsing this convention, the join condition isEmployees.EmployeeID = Orders.EmployeeIDCheck the following diagram. We join the table together we are looking for rows where the EmployeeID matches. So, for every order, where the EmployeeID = 4, the database will match to the Employees table and match to the corresponding row. In this case that is the employee whose last name is “Baker.”
This is called an inner join. Below is a sneak peak of the command, later on, in another article, we get into more details.SELECT Employees.LastName, Orders.ShippedDateFROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeIDThere are several type of Database joins we can use to combine tables together. 

SQL Join Types

There are several join types to consider. In this section we cover the most popular. What distinguishes each join type from one another are the rows returned when a join condition is either met or not met.

Cross Joins

Cross joins return all combinations of rows from each table. So, if you’re looking to find all combinations of size and color, you would use a cross join. Join conditions aren’t used with cross joins. It pure combinatory joy.

Inner Joins

Inner joins return rows when the join condition is met. This is the most common Database join. A common scenario is to join the primary key of once table to the foreign key of another.This is used to perform “lookup,” such are to get the employee’s name from their employeeID.

Outer Joins

Outer joins return all the rows from one table, and if the join condition is met, columns from the other. They differ from an inner join, since an inner join wouldn’t include the non-matching rows in the final result.Consider an order entry system. There may be cases where we want to list all employees regardless of whether they placed a customer order. In this case an outer join comes in handy. When using an outer join all employees, even those not matching orders, are included in the result.There are three types of outer joins: Left, Right, and Full outer joins.Left Outer Join – Return all rows from the “left” table, and matching rows from the “right” table. If there are no matches in the right table, return Null values for those columns.Right Outer Join – Return all rows from the “right” table, and matching rows from the “left” table. If there are no matches in the left table, return Null values for those columns.

See more: Why Do Cats Clean Each Other ? Hint Why Do Cats Lick & Groom Each Other

Full Join – Return all rows from an inner join, when no match is found, return nulls for that table.

What’s next?

In the following articles we dig into the various join types, explore Database joins involving more than one table, and further explain join conditions, especially what can be done with non-equijoin conditions.Also, I think it is important to understand what happens under the covers. So as part of this series, we’ll explore the impact joins have on database performance, and why it is important to understand whether indexes can help reduce query times.Here are some common that come up, once you understand joins:What is the Difference Between a Join and an Union?What is the Difference Between an Inner and Outer Join?What is the Difference Between and Right and Left Outer Join?