Inner and Outer Joins

Outer and Inner SQL Joins

SQL is such a wonderful language and one question I get from people that I talk with is what is the difference between Outer and Inner Joins. Before we talk about that lets talk about what a Join is. A join is used to compare and combine — literally join — and return specific rows of data from two or more tables in a database. It really is that simple. Now an inner join finds and returns matching data from tables, while an outer join finds and returns matching data and some dissimilar data from tables.

Inner Join
An inner join targets on the commonality between two tables. When using an inner join, there must be at least some matching data between two (or more) tables that are being compared. An inner join searches tables for matching or overlapping data. Upon finding it, the inner join combines and returns the information into one new table.

Outer Join
An outer join returns a set of records (or rows) that include what an inner join would return but also includes other rows for which no corresponding match is found in the other table.

There are three types of outer joins:

  • Left Outer Join (or Left Join) – returns only unmatched rows from the left table.
  • Right Outer Join (or Right Join) – returns only unmatched rows from the right table.
  • Full Outer Join (or Full Join) – returns unmatched rows from both tables.

Each of these outer joins refers to the part of the data that is being compared, combined, and returned. Sometimes nulls will be produced in this process as some data is shared while other data is not.

To see examples of LEFT OUTER and RIGHT OUTER JOINS look back at this blog RIGHT JOIN and LEFT JOIN.

Slow SQL

Speeding up Slow Queries inside of Postgres

If your tables are large and you are suffering from slow queries the number one place to start to look, is at the indexes on the tables they are usually the culprit. There are several tools that can help you investigate and dissect the problem you are having with your queries within your Postgres database. In other databases there are similar tools that you can use but in this blog we will only focus on the Postgres components.

PG_STATS_STATEMENTS
The pg_stats_statements component is a awesome place to start to find to not only gathering statistics on the SQL that is being executed against your database but easily identify the slow performing SQL statements.

Once you have this component installed, a system view named pg_stat_statements will be available with all kinds of great information once it has had a chance to consume a good amount of data, look for queries that have relatively high total_time value.

Example: SELECT * FROM pg_stat_statements ORDER BY total_time DESC.

AUTO_EXPLAIN
auto_explain is very useful for finding slow queries but has advantages: First it logs the actual execution plan and supports logging nested statements using the log_nested_statements option. Nested statements are those statements that are executed inside a function. If your application uses many functions, auto_explain is invaluable for getting detailed execution plans. The log_min_duration control which query execution plans are logged, based on how long they perform. For instance, if you set this to 1000, all sql statements that run longer than one second will be logged

Statistics Collector
The statistics collector is a great system that collects all sorts of performance statistics that are extremely helpful in analyzing your slow performing SQL.

Turning on the statistics collector on gives you tons of pg_stat_… views which contain all the handy information. In particular, I have found it to be extremely helpful for finding missing and unused indexes.

EXPLAIN
The EXPLAIN command is a great tool when it comes to tuning queries. It tells you exactly what is really going on within your SQL. To execute it, simply but the EXPLAIN command in front of your statement (i.e EXPLAIN SELECT ……) and run it. PostgreSQL will show you the execution plan it used.

When using EXPLAIN for increasing the performance of your SQL, I suggest using the ANALYZE option (EXPLAIN ANALYZE) as it gives you more accurate results. The ANALYZE option actually executes the statement (rather than just estimating it) and then explains it. In a later BLOG I will go into great depth on the output of the EXPLAIN.

SQL Right Joins

RIGHT JOIN Keyword

Since I did LEFT JOIN the other day, I figured I might as well do a blog on the Right Join to keep it fair.

Define


In SQL the RIGHT JOIN keyword will return all records from the right table (table b), and the matched records from the left table (table a). The result is NULL from the left side, if there is no match.

RIGHT JOIN Syntax

SELECT column_names(s)
FROM table_a
RIGHT JOIN table_b ON table_b.column_name = table_a.column_name

Instructional


In this instruction we will use two tables to demonstrate using RIGHT JOIN within a SQL statement. Lets first define two database tables as well as include the data within those tables.

User table

Id Name Address Zip_Code
5 Bob Vance 45 Geear St 12345
6 Dwight Schrute 55 Sweet St 65565
7 Jim Halbret 65 Nice Rd 43455

Order Table

Id User_Id Employee_Id Order_Date
3 5 67 2019-01-01
45 15 89 2019-01-01
55 16 34 2019-01-01

Example


The following SQL statement will select all users, and any orders they might have.

SQL Demo

SELECT User.Name, Order.Id
FROM Order
RIGHT JOIN User ON Order.User_Id = User.Id
ORDER BY User.Name DESC;

SQL Left Joins

LEFT JOIN Keyword

I wanted to write a quick blog on LEFT JOIN because I get so many questions about this topic from young DB Admin’s and developers as well as Business Managers.

Define


In SQL the LEFT JOIN keyword will return all records from the left table (table a), and the matched records from the right table (table b). The result is NULL from the right side, if there is no match.

LEFT JOIN Syntax

SELECT column_names(s)
FROM table_a
LEFT JOIN table_b ON table_a.column_name = tableb.column_name

Instructional


In this instruction we will use two tables to demonstrate using LEFT JOIN within a SQL statement. Lets first define two database tables as well as include the data within those tables.

User table

Id Name Address Zip_Code
5 Bob Vance 45 Geear St 12345
6 Dwight Schrute 55 Sweet St 65565
7 Jim Halbret 65 Nice Rd 43455

Order Table

Id User_Id Employee_Id Order_Date
3 5 67 2019-01-01
45 15 89 2019-01-01
55 16 34 2019-01-01

Example


The following SQL statement will select all users, and any orders they might have.

SQL Demo

SELECT User.Name, Order.Id
FROM User
LEFT JOIN Order ON User.Id = Order.User_Id
ORDER BY User.Name DESC;