SQL Union and Merge Subset Tables-CodePudding

T1
SCHOOL  STUDENT TEACHER 
1   1   A   
1   2   B   
1   3   B   
1   4   B   
                
        
T2          
SCHOOL  STUDENT TEACHER 
2   7   A   
2   6   A   
2   8   B   
2   9   B


        
T3          
SCHOOL  TEACHER ID  
1   A   FOX 
1   B   CAT 
2   A   DOG 
2   B   MOUSE   
        
        
        
T4  
SCHOOL  STUDENT TEACHER ID
1   1   A   FOX
1   2   B   CAT
1   3   B   CAT
1   4   B   CAT
2   7   A   DOG
2   6   A   DOG
2   8   B   MOUSE
2   9   B   MOUSE

I have T1 T2 T3 where I wish to UNION T1 and T2 and afterwards JOIN T3 such as:

SELECT * FROM T1
UNION
(SELECT * FROM T2)
JOIN
(SELECT SCHOOL, TEACHER, ID
WHERE SCHOOL != 10
FROM T3)

BUT I receive error "UNEXPECTED JOIN"

CodePudding user response：

JOIN is a part of a SELECT statement while UNION are not.

So, you need to make UNION as a part of a SELECT statement

select * from (
  SELECT * FROM T1
  UNION
  SELECT * FROM T2) q1
JOIN
(SELECT SCHOOL, TEACHER, ID
   FROM T3
  WHERE SCHOOL != 10) q2
on /* here goes JOIN condition you need, like q1.school = q2.school*/

Please note:

there is another syntax error in your example: FROM goes always after SELECT and before WHERE
UNION will append result and eliminate duplicate rows which can run slower than UNION ALL. The latter will not check whether there are duplicate rows. So, if you're sure there won't be any duplicates in the result or if it's irrelevant whether you get duplicates or not, you may use UNION ALL