Return value that is not in the other table

Question

I have a query that returns purchase requests on a date, the requests are three, the 1594, 1595 and 1596.. These solitaires have shopping order referring to them that are in another table and that I need to look for as well. But Request 1594 has no purchase order on the other table, that is, when on Where beyond the date, I place that the code of my request is equal to the order order order order order request code, returns only to 1595 and 1596 because the 1594 has no purchase order in the other table, how to do other than fetch the ones that have shopping order fetch the 1594 that has no purchase order in the other purchase order table?

SELECT 
    oc.cd_ord_com AS COD_ORD_COM,
    oc.dt_ord_com AS DATA_ORD_COM,
    oc.dt_prev_entrega AS DATA_PREV_ENTREGA,
    oc.dt_autorizacao AS DATA_AUTORIZACAO,
    oc.cd_sol_com AS COD_SOLIC_COMPRA

FROM 
 
    sol_com sc, ord_com oc
 
WHERE 

    sc.dt_sol_com BETWEEN TO_date ('07/07/2020', 'dd/mm/yyyy')
    AND TO_DATE ('07/07/2020','dd/mm/yyyy')
    AND sc.cd_sol_com = oc.cd_sol_com

Answer 1

I recommend that you cross tables through the JOIN, Besides being easier to read the code is faster.

The way you are currently seeking is this:

FROM sol_com sc

INNER JOIN ord_com oc
  ON sc.cd_sol_com = oc.cd_sol_com
 
WHERE sc.dt_sol_com BETWEEN TO_date ('07/07/2020', 'dd/mm/yyyy')
AND TO_DATE ('07/07/2020','dd/mm/yyyy')

To bring the result you want to use the LEFT JOIN or OUTER JOIN:

FROM sol_com sc

LEFT JOIN ord_com oc
--OUTER JOIN ord_com oc
  ON sc.cd_sol_com = oc.cd_sol_com
 
WHERE sc.dt_sol_com BETWEEN TO_date ('07/07/2020', 'dd/mm/yyyy')
AND TO_DATE ('07/07/2020','dd/mm/yyyy')

INNER JOIN only brings the code contained in the two tables, LEFT JOIN back all data from the first table and if there is data in the second table back data from the second table, the OUTER JOIN back information of the two independent tables if there is the related code of the first or second table.

Take the test and see which one fits your code best.