I have two queries returning the same set of columns.

SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID 
WHERE A.FIRST_NAME IN ('123')

SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID
WHERE B.Address IN ('123')

If the user gives first_name as input the result of first query has to be chosen, if the user gives Address as input, then the result of second query has to be chosen.

I tried using (A.FIRST_NAME = '123' or B.Address = '') when user gives the first name and (A.FIRST_NAME = '' or B.Address = '123') when the user gives the middle name but it's making the query to run forever.

SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID
WHERE A.FIRST_NAME IN ('') OR B.Address IN ('123')

I would like to have suggestions from my fellow experts.

CodePudding user response：

The typical approach would be this:

SELECT a.first_name, a.middle_name, a.age, b.address
FROM table1 a
INNER JOIN table2 b on a.id = b.id 
WHERE (a.first_name = @first_name OR @first_name IS NULL)
  AND (a.address = @address OR @address IS NULL);

This works where

• only the first name is given
• only the address is given
• both are given (in that case only the rows matching both conditions are returned)
• none are given (in that case all rows are returned)

The DBMS will have to find an execution plan that works fine for all conditions. This may be full tables scans that aren't necessarily slow, but this won't be lightning fast either.

This is different with a UNION query:

SELECT a.first_name, a.middle_name, a.age, b.address
FROM table1 a
INNER JOIN table2 b on a.id = b.id 
WHERE a.first_name = @first_name
UNION
SELECT a.first_name, a.middle_name, a.age, b.address
FROM table1 a
INNER JOIN table2 b on a.id = b.id 
WHERE a.address = @address; 

This works where

• only the first name is given
• only the address is given
• both are given (in that case all rows matching at least one condition get returned)
• none are given (in that case no row is returned)

For this query the DBMS can use an index on first_name and another on address (provided you created these indexes) and the query will be very fast.

This UNION query is after all just another way of merlely using the WHERE clause WHERE a.first_name = @first_name OR a.address = @address. But by splitting the query you give the DBMS's optimizer the hint that it can look at this as two separate queries for which it can use different indexes. With a perfect optimizer, this hint would not be necessary and the DBMS would see this itself. I am pretty sure that MySQL's current optimizer needs this hint.)

CodePudding user response：

From the query from your question posted below:

SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address FROM table1 A INNER JOIN table2 B on A.ID = B.ID WHERE A.FIRST_NAME IN ('') OR B.Address IN ('123')

This equates to WHERE A.FIRST_NAME IS NOT NULL OR B.Address IN ('123'). It's important to consider ordering in where clauses and the ramifications of the ordering (indexes on columns is a major factor and the order of columns in the index)[EDIT].

The quoted query will actually return all the records where the first names are not null and the record where the address = '123'.

I would suggest using an if statement to execute the correct query based on the user input. Something like this:

SET @isAddress := 1; -- variable that is set to 'Y' for yes, if the user inputs an address. If it's not 1 or greater, then the user has inputted a first name.
SET @userInput := '123'; -- variable that contains the user's input (either address or firstname). 
IF @isAddress > 0 THEN
    SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
    FROM table1 A INNER JOIN table2 B on A.ID = B.ID
    WHERE B.Address = @userInput;
ELSE
    SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
    FROM table1 A INNER JOIN table2 B on A.ID = B.ID 
    WHERE A.FIRST_NAME = @userInput;
END IF;

[EDIT] On second thought you could use a simplified query like this:

SET @isAddress := 1; -- variable that is set to 'Y' for yes, if the user inputs an address. If it's not 1 or greater, then the user has inputted a first name.
SET @userInput := '123'; -- variable that contains the user's input (either address or firstname). 

SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID
WHERE (@isAddress > 0 AND B.Address = @userInput)
   OR (@isAddress = 0 AND A.FIRSTNAME = @userInput);

CodePudding user response：

I would suggest in the hope of lazy evaluation of WHERE clause:

SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID
WHERE A.FIRST_NAME ='somename' OR
B.Address = '123'

Or alternatively:

SELECT A.FIRST_NAME, A.MIDDLE_NAME, A.Age, B.Address
FROM table1 A INNER JOIN table2 B on A.ID = B.ID
WHERE A.FIRST_NAME ='somenam%' AND
B.Address LIKE '12%'

The second variant suits for the four cases:

1. When both clauses are = '' it will return the unfiltered set (LIKE '%');
2. When A.FIRST_NAME = '' and the B.Address != '' it will return the set filtered by B.Address LIKE '12%';
3. When A.FIRST_NAME != '' and the B.Address = '' it will return the set filtered by A.FIRST_NAME LIKE 'somenam%';
4. When both clauses are != '' it will return the set filtered by both fields (LIKE 'somevalue%').