multiple inner join together-CodePudding

I have the following inner joins:

V1:

select distinct f.ci_frec, v.codfs_venta, v.codbeb_venta
from frecuenta f inner join vende v on (codfs_frec = codfs_venta)

V2:

select distinct g.ci_gusta, v.codfs_venta, g.codbeb_gusta
from vende v inner join gusta g on (codbeb_venta = codbeb_gusta)

Now, I want V3 = V1 - V2, which is why I've used an EXCEPT statement as follows:

select distinct f.ci_frec, v.codfs_venta, v.codbeb_venta
from frecuenta f inner join vende v on (codfs_frec = codfs_venta)
EXCEPT
select distinct g.ci_gusta, v.codfs_venta, g.codbeb_gusta
from vende v inner join gusta g on (codbeb_venta = codbeb_gusta)

So far everything works as expected. My issue now comes that I want to have V4 resulting from having an inner join between V3 and V1, however I do not know how I can make this. Is there a way I can name the inner joins I've already made to do something such as

select *
from V3 inner join V1 on (V3.codbeb_gusta = V1.codbeb_gusta)

or do I need to use a lot of parenthesis and build some sort of "outer" inner join with the two original inner joins V1 and V2 inside it?

Thanks in advance

CodePudding user response：

You can use WITH. You can read about it here

WITH V1 AS (
    SELECT DISTINCT f.ci_frec, v.codfs_venta, v.codbeb_venta
    FROM frecuenta f INNER JOIN vende v on (codfs_frec = codfs_venta)
), V2 AS (
    SELECT DISTINCT g.ci_gusta, v.codfs_venta, g.codbeb_gusta
    FROM vende v INNER JOIN gusta g on (codbeb_venta = codbeb_gusta)
), V3 AS (
    SELECT * FROM V1
    EXCEPT
    SELECT * FROM V2
)
SELECT *
FROM V3
INNER JOIN V1 on (V3.codbeb_gusta = V1.codbeb_gusta)