Forcing the Order of Join Processing : Table Join « Table Join « SQL Server / T-SQL Tutorial






3>
4> CREATE TABLE Departments(
5> Deptno   int         NOT NULL CONSTRAINT PK_dept_deptno PRIMARY KEY,
6> deptname varchar(15) NOT NULL
7> )
8> GO
1>
2> CREATE TABLE Jobs(
3> jobid   int         NOT NULL CONSTRAINT PK_jobs_jobid PRIMARY KEY,
4> jobdesc varchar(15) NOT NULL
5> )
6> GO
1>
2> CREATE TABLE Employees(
3> empid   int         NOT NULL CONSTRAINT PK_emps_empid PRIMARY KEY,
4> empname varchar(10) NOT NULL,
5> deptno  int         NULL CONSTRAINT FK_emps_depts REFERENCES Departments(deptno),
6> jobid   int         NOT NULL CONSTRAINT FK_emps_jobs REFERENCES Jobs(jobid),
7> salary decimal(7,2) NOT NULL
8> )
9> GO
1>
2> INSERT INTO Departments VALUES(100, 'Java2sing')
3> INSERT INTO Departments VALUES(200, 'Production')
4> INSERT INTO Departments VALUES(300, 'Marketing')
5> INSERT INTO Departments VALUES(400, 'Management')
6> INSERT INTO Jobs VALUES(10, 'Java2s')
7> INSERT INTO Jobs VALUES(20, 'Oracle')
8> INSERT INTO Jobs VALUES(30, 'MySQL')
9> INSERT INTO Jobs VALUES(40, 'SqlServer')
10> INSERT INTO Employees VALUES(1, 'Joe', 400, 30, 3456.00)
11> INSERT INTO Employees VALUES(2, 'James', 200, 20, 4325.00)
12> INSERT INTO Employees VALUES(3, 'Chris', 100, 10, 8952.00)
13> INSERT INTO Employees VALUES(4, 'Rob', 400, 30, 1234.00)
14> INSERT INTO Employees VALUES(5, 'Linda', 400, 30, 4567.00)
15> INSERT INTO Employees VALUES(6, 'Lisa', NULL, 30, 8765.00)
16> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2> SELECT
3>   empid,
4>   empname,
5>   salary,
6>   E.deptno,
7>   deptname,
8>   E.jobid,
9>   jobdesc
10> FROM
11>     Employees AS E
12>   JOIN
13>     Departments AS D ON E.deptno = D.deptno
14>   JOIN
15>     Jobs AS J ON E.jobid = J.jobid
16> OPTION(FORCE ORDER)
17>
18> drop table Employees
19> drop table jobs
20> drop table Departments
21> GO
empid       empname    salary    deptno      deptname        jobid       jobdesc
----------- ---------- --------- ----------- --------------- ----------- ---------------
          1 Joe          3456.00         400 Management               30 MySQL
          2 James          4325.00         200 Production               20 Oracle
          3 Chris        8952.00         100 Java2sing              10 Java2s
          4 Rob          1234.00         400 Management               30 MySQL
          5 Linda        4567.00         400 Management               30 MySQL

(5 rows affected)
1>








4.1.Table Join
4.1.1.SQL Server 2005 join types fall into three categories: inner, outer, and cross.
4.1.2.Selecting authors and titles using only joins.
4.1.3.A SELECT statement that joins the Bankers and Billings tables
4.1.4.Joining Tables in the WHERE Clause (not ANSI standard)
4.1.5.Using the GROUP BY Clause
4.1.6.Joining three tables.
4.1.7.Joins and Subqueries
4.1.8.Left and Right Outer Joins
4.1.9.Joining Tables in the FROM Clause (ANSI standard)
4.1.10.Table Aliasing
4.1.11.Join tables with two columns
4.1.12.The result of the previous join is then joined to another table
4.1.13.SQL-92 Three-Way Inner Joins
4.1.14.Forcing the Order of Join Processing