Join query in SQL Server

The query result can contain data from multiple tables or table value objects. To combine data from multiple Table value objects, you can use the SQL join operation.

 

 

Automatic table join

When two or more tables are added to a query, the query and view designer tries to determine whether they are related. If related, the query and view designer automatically adds a connection between the rectangles that represent the table or table structure object.

If the following conditions are met, the query and view designer recognizes the table as a joined table:

• The database contains information related to the specified table.

• If one of the two tables has the same name and data type. This column must be a primary key in at least one table. For example, if you add a table named "employee" and "Jobs", if the job_id column is the primary key of the jobs table, and each table has a column named "job_id" and has the same data type, the query and view designer will automatically join the two tables.

• The query and view designer detects that a search condition (where clause) is actually a join condition. For example, you can add the tables "employee" and "Jobs" and create a search condition to search for the same values in the job_id columns of the two tables. When performing a search, the query and view designer detects that the search condition can cause a join and then creates a join condition based on the search condition.

Manually join tables

When two or more tables are added to a query, the query and view designer tries to join them based on public data or information stored in the database about how these tables are related. For more information, see How to: automatically join a table. However, if the query and view designer are not automatically connected to these tables, or you want to create other join conditions between these tables, you can manually join these tables.

In addition to columns containing the same information, you can also create a join Based on the comparison between any two columns. For example, if the database contains two tables, titles and roysched, you can compare the value in the ytd_sales column of the titles table with the value in the lorange and hirange columns of the Royal sched table. Creating this link will allow you to find specific titles that, by now, have annual sales within the highest and lowest royalty rates.

Create external join

By default, the query and view designer creates an internal join between tables. If you want to include data rows in the result set that have no matching items in the join table, you can create an external join.

When creating an external join, the order in which the table appears in the SQL statement (reflected in the SQL pane) is very important. The first table to be added becomes the "Left" table, and the second table becomes the "right" table. (The order in which tables appear in the graph pane is not important .) When you specify a left external join or a right external join, the reference order is the order in which these tables are added to the query and the order in which they appear in the SQL statements in the SQL pane.

Create external join

1. Automatically or manually create external connections. For more information, see How to: automatically join a table or how to: manually join a table.

2. In the "relationship diagram" pane, select the connected wiring, select "select all rows from <tablename>" from the "query designer" menu, and select the command, to include tables that you want to include more rows.

   • Select the first table to create a left external join.

   • Select the second table to create a right external join.

   • Select two tables to create a full external join.

When you specify an external connection, the query and view designer modifies the connection to indicate the external connection.

In addition, the query and view designer modifies the SQL statements in the SQL pane to reflect changes in the join type, as shown in the following statements:

SELECT employee.job_id, employee.emp_id, employee.fname, employee.minit, jobs.job_descFROM employee LEFT OUTER JOIN jobs ON employee.job_id = jobs.job_id

Because an external join contains unmatched rows, you can use it to find the rows that conflict with the foreign key constraint. Therefore, you can create an external join and add search conditions to search for rows with the primary key column being null in the rightmost table. For example, the following external join queries the rows in the jobs table without corresponding rows in the employee table:

SELECT employee.emp_id, employee.job_idFROM employee LEFT OUTER JOIN jobs ON employee.job_id = jobs.job_idWHERE (jobs.job_id IS NULL) Join type When a table is joined, the created join type affects the rows in the result set. You can create the following join types: Internal ConnectionOnly the matching rows in the two join tables are displayed. (This is the default connection type in the query and view designer .) For example, you can join the titles table and publishers table to create a result set that displays the name of the publisher for each title. In the internal join, the result set does not contain the title of a publisher without information or a publisher without a title. The SQL statements produced by such join operations may be as follows:   SELECT title, pub_nameFROM titles INNER JOINpublishers ON titles.pub_id = publishers.pub_id Note: When an internal join is created, the column containing null does not match any value, so it is not included in the result set. The null value does not match other null values. External joinA join can be included even if there are no rows in the join table. You can create three variants of an external join to specify the unmatched rows to be included: Left Outer JoinIncluding the first named table ("Left" table, which appears in join All rows in the leftmost clause. Does not include unmatched rows in the right table. For example, the following SQL statement describes such a left external join, which is in the titles table and publishers Join tables to include all titles, even those without Publisher Information:   SELECT titles.title_id, titles.title, publishers.pub_nameFROM titles LEFT OUTER JOIN publishers ON titles.pub_id = publishers.pub_id Right Outer JoinIncluding the second named table ("right" table, which appears in join All rows in the rightmost clause. Does not include unmatched rows in the left table. For example, the right external join between the titles and publishers tables will include all publishers, and even those who have no titles in the titles table. The obtained SQL statement may be as follows:   SELECT titles.title_id, titles.title, publishers.pub_nameFROM titles RIGHT OUTER JOIN publishers ON titles.pub_id = publishers.pub_id Completely external joinIncludes all rows in all join tables, whether or not they match. For example, titles and publishers The full external join between the two tables shows all titles and all publishers, and even the rows that do not match values in the other table.   SELECT titles.title_id, titles.title, publishers.pub_nameFROM titles FULL OUTER JOIN publishers ON titles.pub_id = publishers.pub_id Note: Some databases (such as Oracle) do not support full external connections. Full joinIn the result set of this type of join, each pair of two tables may have one row in pairs. For example In the result set output by the publisher of join (full join), each possible author/publisher combination occupies one row. The obtained SQL statement may be as follows:   SELECT *FROM authors CROSS JOIN publishers