Please enable JavaScript to view this site.

R:BASE 11 Help

Navigation: How To (Forms, Reports, and more) > Queries/Views > Query Builder > Technical Documents

Building Queries Using Joins

Scroll Prev Top Next More

A "Join" is an SQL clause that combines records from two tables in a database. When you perform a join, you specify one column from each table to join on. These two columns contain data that is shared across both tables. You can use multiple joins in the same SQL statement to query data from as many tables as you like. Join types include Inner, Left Outer, Right Outer, and Full Outer.

 

The differences are:

 

Inner Join - returns rows when there is at least one row in both tables that match the join condition

Left Outer Join - returns rows that have data in the left table (left of the JOIN keyword), even if there's no matching rows in the right table  

Right Outer Join - returns rows that have data in the right table (right of the JOIN keyword), even if there's no matching rows in the left table

Full Outer Join: - returns all rows, as long as there's matching data in one of the tables  

 

Most of the time, you'll do an Inner Join, though you will sometimes find it useful to do an Outer Join. For example, you need an Outer Join to get all rows in the following cases:

 

When joining a customer table with an orders table to list the customers who ordered something in the current month as well as those who didn't order anything.

When joining a budget table with an expense table to list each budget item, whether or not there was an expense for that item in the current month.

When comparing a header (master table) on the "one" side of a one-to-many relationship against a detail (transaction table) on the "many" side to see all the rows of data, whether or not they have associated details.