Understanding Joins In SQL

Be it for a data science aspirant or professional, one needs to have a solid grasp of what SQL joins are and how they work when dealing with databases.

The real power of SQL comes from working with data from multiple tables at once. The tables are all part of the same schema in a relational database. The term relational database refers to the fact that the tables within it “relate” to one another — they contain common identifiers that allow information from multiple tables to be combined easily.

If you were to think of each table as a separate circle in a Venn diagram, the inner join would be the shaded area where both circles intersect.

The INNER JOIN keyword selects all rows from the tables as long as a join condition satisfies. This keyword will create a result-set made up of combined rows from both tables where a common field exists.

Here is the syntax for an inner join:

This join statement takes all the records from Table B whether or not they have NULL values and the matching columns from Table A.

Right join returns all the rows of the rightmost table and the matching rows for the leftmost table. RIGHT JOIN is also known as RIGHT OUTER.

Here is the syntax:

The left outer join gives us the rows that are common in both the tables as well as extra rows from the table which are not present in the intersection. In other words, a left join returns all records from the left table and the matched records from the right table.

Below is the syntax:

Full joins are also known as full outer joins. This means that a query would combine data and return records from both tables no matter if they had NULL values.

FULL JOIN creates a result-set by combining the results of the left and right joins, including all the rows. For the rows that do not match. the result-set (joined table) will show NULL values.

The syntax is as follows:

What if you want to join two tables based on a condition?

We can simply do it with :

We can also remove the WHERE clause and concatenate the condition statement with ON using the AND operator like this :

The UNION operator is used to combine the result set of two or more SELECT statements.

Union Syntax:

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

Syntax:

There are a couple of reasons you might want to join tables on multiple foreign keys. The first has to do with accuracy. The second reason has to do with performance. It can occasionally make your query run faster to join on multiple fields, even when it does not add to the accuracy of the query.

Till now we are only joining the tables based on the values of one column. However, this can be done for different columns as well.

Syntax:

The self join is a popular special case of the SQL JOIN. While most JOINs link two or more tables with each other to present their data together, a self join links a table to itself. This is usually done by joining a table to itself just once within a SQL query, but it is possible to do so multiple times within the same query.

The following is the syntax of a SELF JOIN:

SQL JOINs are a way to work with more than one table which will allow you to combine data from two or more tables within one SQL query.

Without SQL JOIN, you couldn’t do much in SQL. This is one of the basic things that you need to master to be comfortable using syntax SQL and use its huge potential.

It’s just a topic you need to know if you want to be a data scientist, engineer, or analyst. These are the basics of SQL and we need them to be able to write better reports and compile and combine data from many sources in one SQL query.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

References

Related posts:

Dental plaque is a soft, sticky deposit that forms on the teeth. It’s made up of bacteria, saliva, and food debris. Plaque can cause tooth decay and gum disease if it’s not removed regularly. Yellow…

I remember the first time I kissed a trans woman, I also went skinny dipping. I went from 0 to 60 pretty quickly that night. I was a 22-year-old, had graduated from Indiana University, was living at…

Hunian adalah kebutuhan primer manusia. Hampir seluruh manusia, terutama yang tinggal di wilayah urban, beraktivitas di gedung dan menggunakannya setiap hari. Kita seringkali menerimanya begitu saja…