sales and purchases in same MariaDB table

Question

I have a vehicle table with field with date of purchase and I can retrive info with

SELECT
    MONTH(vehiculo.fecha_compra) mes,
    SUM(vehiculo.precio_compra) importe,
    COUNT(*) cantidad
FROM
    vehiculo
WHERE
    YEAR(vehiculo.fecha_compra) = '2024'
GROUP BY
    MONTH(vehiculo.fecha_compra);

And I have a second table with data about sales that can be query like:

SELECT
    MONTH(propuesta.fecha) mes,
    SUM(propuesta.cotizacion) importe,
    COUNT(*) cantidad
FROM
    propuesta
WHERE
    estado = "Vendido" AND YEAR(propuesta.fecha) = '2024'
GROUP BY
    MONTH(propuesta.fecha)

with results like:

I would have a combination between two tables to obtain something like:

Main problem is that there can be some months with purchassing but not with sales.

Answer 1

You need a full outer join. However, MySQL [still] does not implement them. You can simulate it by union-ing a left join with an anti-semi-join. You can do:

with
purchases as (
  -- first query here
),
sales as (
  -- second query here
)
select p.mes, 
  p.importe as importe_compras, p.cantidad as cantidad_compras,
  s.importe as importe_ventas, s.cantidad as cantidad_ventas
from purchases p
left join sales s on s.mes = p.mes
union all -- from here on, this includes months with sales buy no purchases
select s.mes, null, null, s.importe, s.cantidad
from sales s
left join purchases p on p.mes = s.mes
where p.mes is null

If there will never be months with sales an no purchases you can simplify the query by removing the tail of it (that starts with union all).