is these two queries same?
-
Hi all, Is there any difference between the following two sets of queries?
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
UNION
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS as STATUS, '' , ''
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.ID_NUM = a.ID_NUM
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))and
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
where LOWER(b.LAST_NAME) like '%ram%'
and (a.RESIDSTS = 'R' or a.RESIDSTS <> 'R') and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))well in one there is "UNION" used but will there any difference in the output or are these both queries same as per the output is concered?
suchita
-
Hi all, Is there any difference between the following two sets of queries?
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
UNION
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS as STATUS, '' , ''
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.ID_NUM = a.ID_NUM
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))and
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
where LOWER(b.LAST_NAME) like '%ram%'
and (a.RESIDSTS = 'R' or a.RESIDSTS <> 'R') and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))well in one there is "UNION" used but will there any difference in the output or are these both queries same as per the output is concered?
suchita
Yes, those should give the same result. They might be a bit different sorted I think, but in the end the same data should be in the output. Edit: I oversaw one small thing. The second query in the union doesn't return the values of d.ROOM_DESC, d.ROOM_PHONE, but rather two empty strings in those columns. In your combined query these columns will be filled.
-
Yes, those should give the same result. They might be a bit different sorted I think, but in the end the same data should be in the output. Edit: I oversaw one small thing. The second query in the union doesn't return the values of d.ROOM_DESC, d.ROOM_PHONE, but rather two empty strings in those columns. In your combined query these columns will be filled.