Why query for table joins?

Query employee information with department name SALES

How do I join queries to tables?

Two ways: Sql 1992 and sql1999

sql1992
sql classification
Cartesian product (table multiplier)
2. Use'='for the join condition of the equivalence join table
3. Join conditions for non-equal join tables use'>, >=, <, <=,!=, any etc'.
4. Connect yourself to connect yourself
5. External connections
1. Left outer connection, "(+)" to right of equal sign
2. Right outer connection, "(+)" to left of equal sign
3. To which side of the column'(+)'the table is null-supplemented

--1992
--Grammar
/*

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

where followed by table join condition
Connection type:
Cartesian Product (Table X)

 select * from emp,dept;

2. Equivalent connections (columns = columns)
* Query employee's department name

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno

3. Non-equivalent connections (columns!=columns)
* Query employee's salary level

select e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal

4. Self-connection (table connects itself)
Query the name of the employee's supervisor: Show as "xxx is xxx"

 select e1.ename||'The boss is'|| e2.ename from emp e1,emp e2 where e1.mgr = e2.empno

5. Outer joins (joins another table based on satisfying that the contents of one table are displayed, if the joins match, the joins do not match, and the other table complements null)

  select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno (+

"(+)" is on the right, and we call this query a "left outer join query"
"(+)" is on the left, so we call this query "Right Outer Join Query"
On which side of the equal sign'(+)'fills null in which table

--Disadvantages of sql 1992 1: Joining conditions and filtering of tables are put together
--Query Department names whose employees are paid more than 2000

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.sal >2000

--Disadvantages of sql 1992: Outer connections are not easy to identify

sql1999
sql classification
1.cross join Cross join (Cartesian product), no on keyword required
2.natural join Natural join (find the same column in two tables for equivalence matching) does not require on keyword
3. Connection within inner join
(1) Must have on keyword, on denotes connection condition
2)inner keyword can be omitted
4.outer join outer join, outer keyword can be omitted
        1) left outer join
        2) right outer join
        3) full outer join

--sql 1999

Grammar:

select a.column,b.column from table A join type join table B on join condition

Connection type:
1.CROSS JOIN cross-connect is the Cartesian product

select * from emp cross join dept

2.NATURAL JOIN Natural Join, find two tables with the same name column, and make an equivalence join

   select * from emp natural join dept

3.INNER JOIN inner join (inner keyword can be omitted)
-- Equivalent Connection

  select e.ename,d.dname from emp e  INNER JOIN  dept d on e.deptno = d.deptno where e.sal > 2000

* Non-equivalent connections

   select e.ename,s.grade from emp e INNER JOIN salgrade s on e.sal between s.losal and s.hisal

* Self-connect

        select e1.ename || 'The boss is' || e2.ename from emp e1 INNER JOIN emp e2 on e1.mgr = e2.empno

--Outer join (the outer keyword can be omitted)
4.LEFT OUTER JOIN

 select * from emp e LEFT OUTER JOIN dept d on e.deptno = d.deptno

5.RIGHT OUTER JOIN

  select * from emp e RIGHT OUTER JOIN dept d on e.deptno = d.deptno

6.FULL OUTER JOIN

 select * from emp e FULL OUTER JOIN dept d on e.deptno = d.deptno

--How to join multiple tables

--Query the employee's name, salary, Department name, and salary level

select e.ename, e.sal, d.dname, s.grade
  from emp e, dept d, salgrade s
 where e.deptno = d.deptno
   and e.sal between s.losal and s.hisal

select e.ename, e.sal, d.dname, s.grade
  from emp e
  join dept d
    on e.deptno = d.deptno
  join salgrade s
    on e.sal between s.losal and s.hisal