Except operations for attribute subtraction sets in SQL for two different tables

SQL uses the EXCEPT keyword for the two set subtraction operation, and the syntax format is as follows:

SELECT  from table_name1 EXCEPT SELECT  from Table_name2

The output is a tuple that exists in the previous collection and does not exist in the second collection.

If you choose two tuples, what happens if you have a different tuple name?

Using the example you used earlier, build the code as follows:

Create TableEmployee (EmpNameChar(6), Numchildrenint)Create TableDependent (DepnameChar(6), ageint)Insert  intoEmployeeValues('Haohao',2);Insert  intoEmployeeValues('Haohao',4);Insert  intoEmployeeValues('FGSD',5);Insert  intoEmployeeValues('HDS',7);Insert  intoEmployeeValues('Hauuo',9);Insert  intoEmployeeValues('Hsao',4);Insert  intoEmployeeValues('Hhao',5);Insert  intoDependentValues('Haohao',2);Insert  intoDependentValues('HDGSO',2);Insert  intoDependentValues('Hreo',2);Insert  intoDependentValues('Hjh',2);Insert  intoDependentValues('Haaao',2);

For two worksheets with different attributes, use the except action:

(Select  empname,numchildrenfrom  employee)except(  Select  depname,age from dependent)

The output results are as follows:

"HDS"; 7
"Hhao"; 5
"Hsao"; 4
"Hauuo"; 9
"Haohao"; 4
"FGSD"; 5

The names of the columns are "EmpName" and "Numchildren" and are the attributes of the employee.

After testing, the except operation will also filter out the results according to the definition.

　　

　　Now, play some tricks.

If so, what would happen if the number of attributes for two tables were different?

Build a new data table with the following code:

Create Table Newone (newname     Char(6), newnum    int, tel          int)

Insert data:

Insert  intoNewoneValues('Haohao',2,6);Insert  intoNewoneValues('Haohao',5,6);Insert  intoNewoneValues('Haoyo',2,6);Insert  intoNewoneValues('H6ao',2,6);Insert  intoNewoneValues('Hrhao',2,6);

A statement error appears with the result:

Error: Each EXCEPT query must have the same number of fields
Line 4: (Select Depname,age
^

Error **********

Error: Each EXCEPT query must have the same number of fields
SQL state:42601

character:57

Except operations for attribute subtraction sets in SQL for two different tables