ANSI join syntax: join on : ANSI Join Syntax « Table Joins « SQL Server / T-SQL






ANSI join syntax: join on


16> CREATE TABLE employee  (emp_no    INTEGER NOT NULL,
17>                         emp_fname CHAR(20) NOT NULL,
18>                         emp_lname CHAR(20) NOT NULL,
19>                         dept_no   CHAR(4) NULL)
20>
21> insert into employee values(1,  'Matthew', 'Smith',    'd3')
22> insert into employee values(2,  'Ann',     'Jones',    'd3')
23> insert into employee values(3,  'John',    'Barrimore','d1')
24> insert into employee values(4,  'James',   'James',    'd2')
25> insert into employee values(5,  'Elsa',    'Bertoni',  'd2')
26> insert into employee values(6,  'Elke',    'Hansel',   'd2')
27> insert into employee values(7,  'Sybill',  'Moser',    'd1')
28>
29> select * from employee
30> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
emp_no      emp_fname            emp_lname            dept_no
----------- -------------------- -------------------- -------
          1 Matthew              Smith                d3
          2 Ann                  Jones                d3
          3 John                 Barrimore            d1
          4 James                James                d2
          5 Elsa                 Bertoni              d2
          6 Elke                 Hansel               d2
          7 Sybill               Moser                d1

(7 rows affected)
1>
2>
3> CREATE TABLE department(dept_no   CHAR(4) NOT NULL,
4>                         dept_name CHAR(25) NOT NULL,
5>                         location  CHAR(30) NULL)
6>
7> insert into department values ('d1', 'developer',   'Dallas')
8> insert into department values ('d2', 'tester',      'Seattle')
9> insert into department values ('d3', 'marketing',  'Dallas')
10>
11> select * from department
12> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)
dept_no dept_name                 location
------- ------------------------- ------------------------------
d1      developer                 Dallas
d2      tester                    Seattle
d3      marketing                 Dallas

(3 rows affected)
1>
2> -- ANSI join syntax:
3>
4> SELECT employee.*, department.*
5>          FROM employee INNER JOIN department
6>          ON employee.dept_no = department.dept_no
7>
8> drop table employee
9> drop table department
10> GO
emp_no      emp_fname            emp_lname            dept_no dept_no dept_name                 location
----------- -------------------- -------------------- ------- ------- ------------------------- ----------------
          1 Matthew              Smith                d3      d3      marketing                 Dallas
          2 Ann                  Jones                d3      d3      marketing                 Dallas
          3 John                 Barrimore            d1      d1      developer                 Dallas
          4 James                James                d2      d2      tester                    Seattle
          5 Elsa                 Bertoni              d2      d2      tester                    Seattle
          6 Elke                 Hansel               d2      d2      tester                    Seattle
          7 Sybill               Moser                d1      d1      developer                 Dallas

(7 rows affected)
1>
           
       








Related examples in the same category

1.Reference table name
2.ANSI join syntax: join three tables