Vitor Cardoso Vitor Cardoso - 6 months ago 13
SQL Question

How use left join and/or Right Join and/or Inner Join

I've use JOIN in this query, i don't know how but i used direct reference and the result is corret... Pls Help me

Thanks

Question:
What's proprieties name where license plate (placa) = LVU9132

SELECT P.NOME
FROM MODELO M
,VEICULO V
,PROPRIETARIO P
WHERE P.COD_PROP = V.PROPRIETARIO
AND V.MODELO = M.COD_MOD
AND V.PLACA = 'LVU9132';

Answer

Your query in the new syntax:

SELECT P.NOME
FROM MODELO M 
inner join VEICULO V on V.MODELO = M.COD_MOD
inner join PROPRIETARIO P on P.COD_PROP = V.PROPRIETARIO
WHERE V.PLACA = 'LVU9132';

An inner join requires both matching rows to be present. If the matching PROPRIETARIO row is not found, the VEICULO row won't display either.

Use the following if you want to see the vehicle row even if it has no matching proprietor. The left join return nulls for right hand table PROPRIETARIO if no matching rows found. Note that left join requires the left hand table to be present but doesn't care if there are no matching rows on right hand table.

SELECT P.NOME
FROM MODELO M,VEICULO V,PROPRIETARIO P
WHERE V.PROPRIETARIO *= P.COD_PROP      -- left outer join
    AND V.MODELO = M.COD_MOD
    AND V.PLACA = 'LVU9132';

New syntax (ANSI92 standard)

SELECT P.NOME
FROM MODELO M 
inner join VEICULO V on V.MODELO = M.COD_MOD
left join PROPRIETARIO P on P.COD_PROP = V.PROPRIETARIO
WHERE V.PLACA = 'LVU9132';

The old-style syntax is no longer supported in SQL Server 2012, because it can return incorrect results.

Comments