Cross-connection and full-connection

Source: Internet
Author: User

Cross-connection and full-connection
In addition to displaying rows that meet the conditions of the join, the results also show all rows that meet the search conditions in the tables on both sides of the join operation.
SQL> select e. empno, e. ename, e. sal, d. grade
2 from emp e full outer join salgrade d
3 on e. sal between d. losal and d. hisal;
EMPNO ENAME SAL GRADE
----------------------------------------
7839 KING 5000 5
7902 FORD 3000 4
7788 SCOTT 3000 4
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7499 ALLEN 1600 3
7844 TURNER 1500 3
7934 MILLER 1300 2
7521 WARD 1250 2
7654 MARTIN 1250 2
7876 ADAMS 1100 1
7900 JAMES 950 1
7369 SMITH 800 1
7937 Candy 500 6

In the above result
7937 Candy 500
This data item does not have a corresponding wage level,
6
This data does not have the corresponding employee information, and the right outer connection




Cross join
Cross joi is used to implement cross join. You can connect two tables. The result is a combination of all the data in each row of the two tables, that is, the Cartesian product of all data rows in the two tables.
Cross join is very similar to simple join operations. The difference is that when using a cross join, multiple table names in the from clause are separated by the cross join keyword instead of commas. Additionally, you do not need to use the keyword on to limit the connection conditions in the Cross-join operation. However, you can add a where clause to set the connection conditions.
SQL> select empno, ename, e. deptno, d. deptno
2 from emp e cross join dept d;
EMPNO ENAME DEPTNO
----------------------------------------
7369 SMITH 20 10
7499 ALLEN 30 10
7521 WARD 30 10
7566 JONES 20 10
7654 MARTIN 30 10
7698 BLAKE 30 10
7782 CLARK 10 10
7788 SCOTT 20 10
7839 KING 10 10
7844 TURNER 30 10
7876 ADAMS 20 10
7900 JAMES 30 10
7902 FORD 20 10
7934 MILLER 10 10
7937 Candy 10
7369 SMITH 20 20
7499 ALLEN 30 20
7521 WARD 30 20
7566 JONES 20 20
7654 MARTIN 30 20
7698 BLAKE 30 20
7782 CLARK 10 20
7788 SCOTT 20 20
7839 KING 10 20
7844 TURNER 30 20
7876 ADAMS 20 20
7900 JAMES 30 20
7902 FORD 20 20
7934 MILLER 10 20
7937 Candy 20
7369 SMITH 20 30
7499 ALLEN 30 30
7521 WARD 30 30
7566 JONES 20 30
7654 MARTIN 30 30
7698 BLAKE 30 30
7782 CLARK 10 30
7788 SCOTT 20 30
7839 KING 10 30
7844 TURNER 30 30
7876 ADAMS 20 30
7900 JAMES 30 30
7902 FORD 20 30
7934 MILLER 10 30
7937 Candy 30
7369 SMITH 20 40
7499 ALLEN 30 40
7521 WARD 30 40
7566 JONES 20 40
7654 MARTIN 30 40
7698 BLAKE 30 40
7782 CLARK 10 40
7788 SCOTT 20 40
7839 KING 10 40
7844 TURNER 30 40
7876 ADAMS 20 40
7900 JAMES 30 40
7902 FORD 20 40
7934 MILLER 10 40
7937 Candy 40
Select 60 rows.
// If no conditions are used for cross join, the result is the flute product of the data, that is, all the combinations of the data in each table and the data in another table.
SQL> select empno, ename, e. deptno, d. deptno
2 from emp e cross join dept d
3 where d. deptno = 10
4 and e. deptno = 10;
EMPNO ENAME DEPTNO
----------------------------------------
7782 CLARK 10 10
7839 KING 10 10
7934 MILLER 10 10
// Restrict deptno in two tables











































Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.