Check value if exist in another table within Select Query

I have three tables first table stores user basic Info, the second table stores that user who exist in LDAP directory and third table store common information of both tables.

below is the table structure

I want name from a table if the value is not matched then check to another table. the desired output looks like. I tried to achieve this through UNION but table have more than 70K records. it’s slowing the performance. Query

select Distinct
userInfoID AS 'UserInfoId',
accountName AS 'AccountName',
accountType AS 'AccountType',
privilege AS 'Privilege',
active AS 'IsActive',
org AS 'OrgName',
(L.firstName + ' ' + L.lastName) AS 'Name',
emailId AS 'EmailAddress'
FROM tbl_UserInfo U
LEFT JOIN tbl_ldapUser L ON L.memberId = U.memberId

UNION

select Distinct
userInfoID AS 'UserInfoId',
accountName AS 'AccountName',
accountType AS 'AccountType',
privilege AS 'Privilege',
active AS 'IsActive',
org AS 'OrgName',
(M.firstName + ' ' + M.lastName) AS 'Name',
emailId AS 'EmailAddress'
FROM tbl_UserInfo U
LEFT JOIN tbl_Member M ON M.memberId = U.memberId

Answer

This is impossible to test, however, I suspect this will be far more performant. Firstly I change the JOINs to an INNER JOIN, rather than a LEFT JOIN. If the row in tbl_UserInfo could have no matching rows in both tbl_ldapUser and tbl_Member, and you still want that row returned, then change the bottom query to a LEFT JOIN (not the top).

I remove both the DISTINCTs as these are just additional overhead; you were using UNION so you have already stated to the data engine you want distinct rows. I have, however, also changed the UNION to a UNION ALL, which removes the distinct clause entirely; this is likely why your query was slow as DISTINCT can be a very expensive operator.

Finally, I use EXISTS instead to check if the row existed in the tbl_ldapUser table in the bottom query. This stops a user being returned twice when matched against both tables:

SELECT userInfoID AS UserInfoId,
       accountName AS AccountName,
       accountType AS AccountType,
       privilege AS Privilege,
       active AS IsActive,
       org AS OrgName,
       (L.firstName + ' ' + L.lastName) AS [Name],
       emailId AS EmailAddress
FROM tbl_UserInfo U
     JOIN tbl_ldapUser L ON L.memberId = U.memberId
UNION ALL
SELECT userInfoID AS UserInfoId,
       accountName AS AccountName,
       accountType AS AccountType,
       privilege AS Privilege,
       active AS IsActive,
       org AS OrgName,
       (M.firstName + ' ' + M.lastName) AS Name,
       emailId AS EmailAddress
FROM tbl_UserInfo U
     JOIN tbl_Member M ON M.memberId = U.memberId
WHERE NOT EXISTS (SELECT 1
                  FROM tbl_ldapUser L
                  WHERE L.memberId = U.memberId);

Also note I remove the single quotes around the aliases. This is a bad habit. Single quotes are for literal strings, and (even though it is supported) should not be used for aliasing. It can often be confusing to read and people new to the language can think that the syntax works elsewhere. For example ORDER BY 'UserInfoId' would not order by the column aliased using 'UserInfoId' but by the literal string 'UserInfoId'; meaning the order is completely arbitrary (as every row has the same value, 'UserInfoId').