SQL JOINS

MySQL connection outside

The first conclusion

Keep these pictures in mind when learning about inner and outer connections.

  1. Set A does not contain set B(outer join)

select * from TableA A left join TableB B ON A.KEY = B.KEY WHERE B.KEY IS NULL;
Copy the code
  1. Set Part A (outer join)

select * from TableA A left join TableB B ON A.KEY = B.KEY;
Copy the code
  1. Set A intersects set B (inner join)

select * from TableA join TableB ON A.KEY = B.KEY; Select * from TableA left join TableB ON a.key = b.key WHERE b.key IS NOT NULL; // external connectionCopy the code

External features (left Join as an example)

  • Application scenario: A table has A table that does not. That is, set A does not contain part of set B
  • Features:
  1. There is A primary table and A secondary table. A is the primary table and B is the secondary table.
  2. Query results for all records in the master table
    • If there is a matching value from the table, display it directly
    • Null is displayed if there is no match from the table.
  3. Query result = join result (can be Null) + primary table has secondary table has no part

Out left, out right

  • Left-outside join The left side is the primary table
  • The right join is the primary table

Left and right can interconvert

Specific case

The data table

Get a tableBoys table

test

Query goddess’s boyfriend, null if it does not exist

  • = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
select a.id ,a.name, b.id, b.boyName from beauty a left join boys b on a.boyfriend_id = b.id;
Copy the code

  • Id is the primary key (the primary key usually cannot be null). It indicates that the result cannot be found in the condition judgment, so it can be deleted.

select a.id ,a.name, b.id, b.boyName from beauty a left join boys b on a.boyfriend_id = b.id where b.id is not null;
Copy the code
  • The final result

  • If id = null is removed, the result is the same

All outside

All out = left out + right out union, mysql does not support, but you can use union to achieve the same effect.