Home Database Mysql Tutorial SQL语句技巧:查询存在一个表而不在另一个表中的数据记录

SQL语句技巧:查询存在一个表而不在另一个表中的数据记录

Jun 07, 2016 pm 03:10 PM
sql exist Skill number Inquire statement

方法一(仅适用单个字段) 使用 not in ,容易理解,效率低 select A.ID from A where A.ID not in (select ID from B) 方法二(适用多个字段匹配) 使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录 select A.ID from A left jo

方法一(仅适用单个字段)
使用 not in ,容易理解,效率低

select A.ID from A where A.ID not in (select ID from B)

方法二(适用多个字段匹配)
使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录

select A.ID from A left join B on A.ID=B.ID where B.ID is null 

方法三(适用多个字段匹配)

select * from B where (select count(1) as num from A where A.ID = B.ID) = 0

方法四(适用多个字段匹配)

select * from A where not exists(select 1 from B where A.ID=B.ID)

 

同步发表于我的个人网站:http://www.zuowenjun.cn/post/2014/10/09/54.html

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1659
14
PHP Tutorial
1257
29
C# Tutorial
1231
24
What is the difference between HQL and SQL in Hibernate framework? What is the difference between HQL and SQL in Hibernate framework? Apr 17, 2024 pm 02:57 PM

HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).

12306 How to check historical ticket purchase records How to check historical ticket purchase records 12306 How to check historical ticket purchase records How to check historical ticket purchase records Mar 28, 2024 pm 03:11 PM

Download the latest version of 12306 ticket booking app. It is a travel ticket purchasing software that everyone is very satisfied with. It is very convenient to go wherever you want. There are many ticket sources provided in the software. You only need to pass real-name authentication to purchase tickets online. All users You can easily buy travel tickets and air tickets and enjoy different discounts. You can also start booking reservations in advance to grab tickets. You can book hotels or special car transfers. With it, you can go where you want to go and buy tickets with one click. Traveling is simpler and more convenient, making everyone's travel experience more comfortable. Now the editor details it online Provides 12306 users with a way to view historical ticket purchase records. 1. Open Railway 12306, click My in the lower right corner, and click My Order 2. Click Paid on the order page. 3. On the paid page

How to check your academic qualifications on Xuexin.com How to check your academic qualifications on Xuexin.com Mar 28, 2024 pm 04:31 PM

How to check my academic qualifications on Xuexin.com? You can check your academic qualifications on Xuexin.com, but many users don’t know how to check their academic qualifications on Xuexin.com. Next, the editor brings you a graphic tutorial on how to check your academic qualifications on Xuexin.com. Interested users come and take a look! Xuexin.com usage tutorial: How to check your academic qualifications on Xuexin.com 1. Xuexin.com entrance: https://www.chsi.com.cn/ 2. Website query: Step 1: Click on the Xuexin.com address above to enter the homepage Click [Education Query]; Step 2: On the latest webpage, click [Query] as shown by the arrow in the figure below; Step 3: Then click [Login Academic Credit File] on the new page; Step 4: On the login page Enter the information and click [Login];

A must-have for veterans: Tips and precautions for * and & in C language A must-have for veterans: Tips and precautions for * and & in C language Apr 04, 2024 am 08:21 AM

In C language, it represents a pointer, which stores the address of other variables; & represents the address operator, which returns the memory address of a variable. Tips for using pointers include defining pointers, dereferencing pointers, and ensuring that pointers point to valid addresses; tips for using address operators & include obtaining variable addresses, and returning the address of the first element of the array when obtaining the address of an array element. A practical example demonstrating the use of pointer and address operators to reverse a string.

In-depth understanding of function refactoring techniques in Go language In-depth understanding of function refactoring techniques in Go language Mar 28, 2024 pm 03:05 PM

In Go language program development, function reconstruction skills are a very important part. By optimizing and refactoring functions, you can not only improve code quality and maintainability, but also improve program performance and readability. This article will delve into the function reconstruction techniques in the Go language, combined with specific code examples, to help readers better understand and apply these techniques. 1. Code example 1: Extract duplicate code fragments. In actual development, we often encounter reused code fragments. At this time, we can consider extracting the repeated code as an independent function to

Instead of fighting, play ball! Introduction to the gameplay of the new mode of 'Eternal Tribulation': Hot-Blooded Fighting Ball Instead of fighting, play ball! Introduction to the gameplay of the new mode of 'Eternal Tribulation': Hot-Blooded Fighting Ball Apr 11, 2024 pm 01:52 PM

Tired of playing ranked? Then come and play ball! Teammates who cooperate tacitly can give opponents a taste of surprise! In the ever-changing battlefield, try to see how you can use your skills to break the situation with one move! "Eternal Tribulation" has launched a new team confrontation mode in the update on April 11. The following is an introduction to the "Eternal Tribulation" hot-blooded fighting ball activity. Overview of how to play the everlasting hot-blooded fighting ball: Use various means to bounce the fighting ball and defeat the enemy. Number of people: 9 people in single row, 6 people in three rows. How to play: Round: 1. The game is divided into multiple rounds. At the beginning of each round, Douqiu will be born in the center of the field and randomly select a player to chase. Players use various means to attack. The ball will speed up the fighting ball, and being hit by the fighting ball will deduct your stamina. 2. Each person in the single row has one point of physical strength each round, and each person in the three rows has one point of physical strength each round.

Top 10 Global Digital Virtual Currency Trading Platform Ranking (2025 Authoritative Ranking) Top 10 Global Digital Virtual Currency Trading Platform Ranking (2025 Authoritative Ranking) Mar 06, 2025 pm 04:36 PM

In 2025, global digital virtual currency trading platforms are fiercely competitive. This article authoritatively releases the top ten digital virtual currency trading platforms in the world in 2025 based on indicators such as transaction volume, security, and user experience. OKX ranks first with its strong technical strength and global operation strategy, and Binance follows closely with high liquidity and low fees. Platforms such as Gate.io, Coinbase, and Kraken are at the forefront with their respective advantages. The list covers trading platforms such as Huobi, KuCoin, Bitfinex, Crypto.com and Gemini, each with its own characteristics, but investment should be cautious. To choose a platform, you need to consider factors such as security, liquidity, fees, user experience, currency selection and regulatory compliance, and invest rationally

How to copy a form on DingTalk__ Tips for copying forms on DingTalk How to copy a form on DingTalk__ Tips for copying forms on DingTalk Mar 29, 2024 pm 03:11 PM

1. After successfully logging into the mobile DingTalk APP, click the [Work] tab at the bottom of the page. 2. Enter the DingTalk work page and click the [Smart Form Filling] option under [Collaboration Efficiency]. 3. Enter the smart form filling page and click the [Statistics] tab in the lower right corner. 4. Click the [...] icon of the form you want to copy. 5. On the form operation page, click the [Copy Form] option. 6. The form has been copied. On the form editing page, you can edit the form title and various contents. Click the [Finish] button in the lower right corner to publish the form.

See all articles