Query with multiple Mysql SELECT’s

Question

SELECT prefixo FROM getVeiculosLinha AS V JOIN getLinhas AS L ON V.cod_linha = L.cod_linha JOIN tab_garagens AS G ON V.prefixo = G.onibus_prefixo

SELECT c.local FROM status AS s JOIN cadastro_pontos AS c ON c.onibus_ponto = s.onibus_ponto JOIN tab_garagens AS t ON t.onibus_prefixo = c.local WHERE data > (NOW() - INTERVAL 10 MINUTE) GROUP BY s.onibus_ponto

The two queries are distinct queries in which I would like to generate a third query with the data that NAY are duplicated; remembering that both have a common column (c.local and prefix) that bring the same information but from different places.

Answer 1

Since Mysql does not implement FULL OUTER JOIN simulate with UNION from LEFT JOIN and RIGHT JOIN and using CTE to simplify:

WITH
  cte1 AS (SELECT prefixo FROM getVeiculosLinha AS V JOIN getLinhas AS L ON V.cod_linha = L.cod_linha JOIN tab_garagens AS G ON V.prefixo = G.onibus_prefixo),
  cte2 AS (SELECT c.local FROM status AS s JOIN cadastro_pontos AS c ON c.onibus_ponto = s.onibus_ponto JOIN tab_garagens AS t ON t.onibus_prefixo = c.local WHERE data > (NOW() - INTERVAL 10 MINUTE) GROUP BY s.onibus_ponto)
  
SELECT prefixo, local FROM cte1 LEFT JOIN cte2 ON (prefixo = local) WHERE local IS NULL
UNION
SELECT prefixo, local FROM cte1 RIGHT JOIN cte2 ON (prefixo = local) WHERE prefixo IS NULL;