Creating Joins

Create a join in a SELECT statement to retrieve data from two or more tables based on matching column values.

Prerequisites 

  1. Connect to the data source through a connection profile.
  2. Open a new or existing SQL file, and be sure to select the connection profile Type, Name, and Database.
  3. In SQL Query Builder, create a SELECT statement.

A join enables you to select data from two or more tables into a single results set without repeating unnecessary data. You can create different kinds of joins depending on what data from each table you want in the results set.

Table 1. Join operators
Join operator Description
Inner join Returns data from all tables based on a common condition.
Left outer join Returns all the values from the left table plus matched values from the right table, and fills in NULLs for any missing values from the right table.
Right outer join Returns all the values from the right table and matched values from the left table, and fills in NULLs for any missing values from the left table.
Full outer join Combines the results of both left and right outer joins. The joined table contains all records from both tables, and fills in NULLs for missing matches on either side.
Note: Some syntactic variations might exist between your database and the SQL syntax that SQL Query Builder supports; consequently, some SQL Query Builder features might not work with your database.
  1. In the Tables pane, add two or more tables
  2. Drag the pointer from a column in one table to a column in another table.

    By default, SQL Query Builder creates an inner join. You can also create a join by right-clicking anywhere in the Tables pane and selecting Create Join.

  3. (Optional) To change the join type from the default inner join, right-click on the connector line and select Specify Join Type.

    All joins defined between the two tables change to the selected join type.

  4. (Optional) To run the SQL code, in the SQL Source pane, right-click and select Run SQL.

    View the results in the SQL Results tab.

  5. Click OK to exit SQL Query Builder and to return to the SQL File Editor, where the SQL code displays in the editor window.

    Clicking Cancel results in no changes to the SQL code in the SQL File Editor. You lose any changes you made in SQL Query Builder.

Related tasks
Creating a SELECT Statement
Creating a FULLSELECT (UNION) Statement
Creating a WITH Statement