Oracle Take the intersection of data in two tables

keyword : Oracle Take the intersection of data in two tables INTERSECT

Oracle As a large relational database , In daily application, it is often necessary to extract the intersection data of two tables

For example, the following table is available , Ask to find out the salary 2500( Not included 2500) Above and male (M) Employee number of , Then we need to use the relationship between the two tables to make an intersection

employee

CODE

NAME

GENDER

001 Tom M
002 Jerry M
003 Ana F
salary

CODE

SALARY

001 2800
002 2500
003 3000

Method 1: Using operators INTERSECT

INTERSECT The operator is used to merge two queries , Returns records that exist in both queries , That is to return the intersection of two query results , The premise is that the number of columns and data types of the two queries must be exactly the same .

SELECT CODE FROM EMPLOYEE WHERE GENDER = 'M'
INTERSECT
SELECT CODE FROM SALARY WHERE SALARY > 2500

Article 1 with a SQL As the result of the

CODE
----------
001
002

Second SQL As the result of the

CODE
----------
001
003

INTERSECT Result

CODE
----------
001

Method 2: Using relationships SQL

SELECT A.CODE FROM EMPLOYEE A, SALARY B
WHERE A.CODE = B.CODE AND A.GENDER = 'M' AND B.SALARY > 2500

The result is

CODE
----------
001

The above two methods are my common methods , Especially the second one , Fully embody the essence of relational data . But if the structure of the two tables is complex , And the relationship conditions are difficult to achieve , The first is obviously a simple way to get the desired results , It's a good way .

Difference sets : minus

Combine , And eliminate duplicate records :union

Combine , And include duplicate records :union all

from :http://blog.sina.com.cn/s/blog_5656bf3e010007cz.html

Oracle Take the intersection of data in two tables and set the difference set

  1. Oracle database -- Solve the huge amount of data in a single table ( big data 、 There's a million levels of data , Check after , It takes a lot of time to update data )

    Copyright notice : This article is an original blog article , No reprint without the permission of the blogger . https://blog.csdn.net/QQ578473688/article/details/54561397 Ideas 1: Use backup tables In the backup table ...

  2. Oracle Insert the data of two tables into the third table and the third table does not exist

    1. It can't be used because it's first checked and then inserted insert into table1() values(), To use insert into table1() select * table2, Out of commission values. 2. ...

  3. SQL Server Take the intersection of two tables in , Union and subtraction

    In the project, we encounter the situation that we want to take the difference set of two tables Suppose there are two tables tblNZPostCodes, NZPostcode   Both tables are New Zealand post code Information , The fields are consistent , It's just a difference in the data . 1. Union ...

  4. solve MySQL For example, I want to pull a user from a message table id by 1 Before 10 The latest data

    We all know , All kinds of mainstream social apps or reading apps , Basically, there are list class views , And they all have the function of sliding to the bottom to load more , The corresponding back end is paging data . The benefits are self-evident , Generally speaking , These data items are arranged in reverse chronological order , Users only care about the latest news ...

  5. Study diary 3、 Opportunism makes the data of two tables in one at the same time treeGrid It shows that

    Don't say more, just go to the code , $('#List').treegrid({ url: '@Url.Action("GetList")', width: $(window).width() - ...

  6. MySQL Query the data record in the data table ( Including multi table queries )

    MySQL Query the data record in the data table ( Including multi table queries ) stay MySQL The purpose of creating a database in is to use the data . Use select The query statement can query the data from the database . select The syntax format of the statement is as follows : sele ...

  7. About crossing DB The incremental ( increase 、 Change ) Synchronization of two tables data tips

    In some cases , We need to isolate different DB, each other DB We can't visit each other , But the actual business scenario needs to start from A DB visit B DB The circumstances of , What to do at this time ? I think there are three conventional solutions as follows : 1. Both sides provide RESET API, You need to visit different DB ...

  8. mysql-- Yes ( The data in the table ) Addition, deletion and modification of

    One . insert data ( increase )insert 1. insert data ( Order insert ) Grammar 1 : INSERT INTO Table name ( Field 1, Field 2, Field 3… Field n) VALUES( value 1, value 2, value 3… value n); # Specify fields to insert data , insert ...

  9. Yes lua The data in the table is processed in a certain format , loop

    function putStartCard(handCard) function dataDeal(array,a,b,c) cclog(" Get into datadeal=============== ...

Random recommendation

  1. ASP.NET MVC5+EF6+EasyUI Background management system (45)- Workflow design - Design steps

    Series catalog Step design is important , Especially the choice of rules . I have a few rules here 1. Press to choose ( When drafting, choose your own examiner , More flexible ) 2. By superior ( Do not need to specify , You need to know who the user's boss is , It can be set in position management , Or in the user table ...

  2. web There are several ways to jump pages

    It can be triggered by client or server to realize page Jump . The client triggers Mode one : Use Javascript utilize window.location Object's href attribute .assign() Method or replace() Method to implement ...

  3. pair Use

    #include<iostream> #include<cmath> #include<cstdio> #include<algorithm> #inc ...

  4. Stanford lesson three : Review of Linear Algebra (Linear Algebra Review)

    3.1   Matrices and vectors 3.2   Addition and scalar multiplication 3.3   Matrix vector multiplication 3.4   Matrix multiplication 3.5   The properties of matrix multiplication 3.6   The inverse . Transposition 3.1   Matrices and vectors Pictured : This is  4×2 matrix , namely  4 That's ok   ...

  5. jquery Medium each() Methods,

    each() The method can make DOM The loop structure is simple , Not easy to make mistakes .each() Function encapsulates a very powerful traversal function , It's also easy to use , It can traverse one-dimensional arrays . Multidimensional arrays .DOM, JSON Wait a minute javaScript Use... In the development process $ ...

  6. golang vim Environment building

    The main use is golang Of vim plug-in unit vim-go, Installation method : (1) Configure your own GOPATH and GOROOT: (2) stay GOPATH Create under directory src.pkg.main.bin Four directories : (3) install ...

  7. Java ==,equals() and hashCode

    Kruger It's about == and equals() The approach is different , After inquiry, sort out the specific content , During the query process, we found that hashCode() Methods and equlas() Close ties , So study together . It's more simple , If you understand more in the future, update at any time ...

  8. from JPG Get thumbnails from

    using System; using System.Drawing; using System.Collections; using System.ComponentModel; using Sys ...

  9. [ Roof placement ] 63 Line code perfect implementation html5 Snake game

    I used to pay little attention to html5, Feel the choice html It's becoming a trend , I want to know more about . I found a game to learn , After writing this game, I feel html5 and js It's very close , If js Not particularly good . It's estimated that we need to do some tutoring first js, This is just a personal suggestion , Different ...

  10. [JAR package ] android introduce JAR package , Pack it up JAR package , Pack it up Library project , Import Library project

    (1) Import the project JAR package :1. Create a... In the project directory libs Catalog , The external jar The package is in it .2. Right click on the project ,Bulid Path->Configure Build Path3. Set up Libraie ...