Join Tables Automatically (Visual Database Tools)
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse
When you add two or more tables to a query, the Query and View Designer attempts to determine if they are related. If they are, the Query and View Designer automatically puts join lines between the rectangles representing the tables or table-structured objects.
The Query and View Designer will recognize tables as joined if:
The database contains information that specifies that the tables are related.
If two columns, one in each table, have the same name and data type. The column must be a primary key in at least one of the tables. For example, if you add
jobstables, if the
job_idcolumn is the primary key in the
jobstable, and if each table has a column called
job_idwith the same data type, the Query and View Designer will automatically join the tables.
The Query and View Designer will create only one join based on columns with the same name and data type. If more than one join is possible, the Query and View Designer stops after creating a join based on the first set of matching columns that it finds.
The Query and View Designer detects that a search condition (a WHERE clause) is actually a join condition. For example, you might add the tables
jobs, then create a search condition that searches for the same value in the
job_idcolumn of both tables. When you do, the Query and View Designer detects that the search condition results in a join, and then creates a join condition based on the search condition.
If the Query and View Designer has created a join that is not suitable to your query, you can modify the join or remove it. For details, see Modify Join Operators (Visual Database Tools) and Remove Joins (Visual Database Tools).
If the Query and View Designer does not automatically join the tables in your query, you can create a join yourself. For details, see Join Tables Manually (Visual Database Tools).