skip to Main Content

I have a large query with the below WHERE clause that I’m hoping may be written in a cleaner / possible more efficient way. Each combination is specific and needs to be accounted for. EDIT Per the first comment i have modified the question. Again this is a very small part of a much larger query, so I’m not quite sure if this is the right route to take.

DB

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `querytest`
--
CREATE DATABASE IF NOT EXISTS `querytest` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `querytest`;

-- --------------------------------------------------------

--
-- Table structure for table `table1`
--

CREATE TABLE `table1` (
  `col0` int(11) NOT NULL,
  `col1` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `table1`
--

INSERT INTO `table1` (`col0`, `col1`) VALUES
(1, 'val1'),
(2, 'val2'),
(3, 'val3');

-- --------------------------------------------------------

--
-- Table structure for table `table2`
--

CREATE TABLE `table2` (
  `col0` int(11) NOT NULL,
  `col1` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `table2`
--

INSERT INTO `table2` (`col0`, `col1`) VALUES
(1, 'val1'),
(2, 'val2'),
(3, 'val3');

-- --------------------------------------------------------

--
-- Table structure for table `table3`
--

CREATE TABLE `table3` (
  `col0` int(11) NOT NULL,
  `col1` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `table3`
--

INSERT INTO `table3` (`col0`, `col1`) VALUES
(1, 'val1'),
(2, 'val2'),
(3, 'val3');

-- --------------------------------------------------------

--
-- Table structure for table `table4`
--

CREATE TABLE `table4` (
  `col0` int(11) NOT NULL,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  `col3` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `table4`
--

INSERT INTO `table4` (`col0`, `col1`, `col2`, `col3`) VALUES
(1, 'val2', NULL, 'val3'),
(2, NULL, NULL, NULL),
(3, NULL, 'val3', NULL),
(4, NULL, NULL, 'val3'),
(5, 'val1', NULL, NULL),
(6, NULL, 'val2', 'val3');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `table1`
--
ALTER TABLE `table1`
  ADD PRIMARY KEY (`col0`),
  ADD KEY `col1` (`col1`);

--
-- Indexes for table `table2`
--
ALTER TABLE `table2`
  ADD PRIMARY KEY (`col0`),
  ADD KEY `col1` (`col1`);

--
-- Indexes for table `table3`
--
ALTER TABLE `table3`
  ADD PRIMARY KEY (`col0`),
  ADD KEY `col1` (`col1`);

--
-- Indexes for table `table4`
--
ALTER TABLE `table4`
  ADD PRIMARY KEY (`col0`),
  ADD KEY `col1` (`col1`),
  ADD KEY `col2` (`col2`),
  ADD KEY `col3` (`col3`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `table1`
--
ALTER TABLE `table1`
  MODIFY `col0` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `table2`
--
ALTER TABLE `table2`
  MODIFY `col0` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `table3`
--
ALTER TABLE `table3`
  MODIFY `col0` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `table4`
--
ALTER TABLE `table4`
  MODIFY `col0` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `table4`
--
ALTER TABLE `table4`
  ADD CONSTRAINT `table4_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `table1` (`col1`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `table4_ibfk_2` FOREIGN KEY (`col2`) REFERENCES `table2` (`col1`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `table4_ibfk_3` FOREIGN KEY (`col3`) REFERENCES `table3` (`col1`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

QUERY

SELECT table4.col0, table4.col1, table4.col2, table4.col3

FROM table4

LEFT JOIN table1
ON table1.col1 = table4.col1

LEFT JOIN table2
ON table2.col1 = table4.col2

LEFT JOIN table3
ON table3.col1 = table4.col3

WHERE 
(
    (
     table1.col1 = 'val1' 
     AND 
     table2.col1 = 'val2' 
     AND 
     table3.col1 = 'val3'
    )

    OR 

    (
     table1.col1 = 'val1' 
     AND 
     table4.col2 IS NULL
     AND 
     table4.col3 IS NULL
    )

    OR 

    (
     table1.col1 = 'val1' 
     AND 
     table2.col1 = 'val2'
     AND 
     table4.col3 IS NULL
    )

    OR 

    (
     table1.col1 = 'val1' 
     AND 
     table4.col2 IS NULL
     AND 
     table3.col1 = 'val3'
    )

    OR 

    (
     table4.col1 IS NULL
     AND 
     table2.col1 = 'val2' 
     AND 
     table4.col3 IS NULL
    )

    OR

    (
     table4.col1 IS NULL
     AND 
     table4.col2 IS NULL
     AND 
     table3.col1 = 'val3'
    )

    OR

    (
     table4.col1 IS NULL
     AND 
     table2.col1 = 'val2' 
     AND 
     table3.col1 = 'val3'
    )

    OR

    (
     table4.col1 IS NULL
     AND 
     table4.col2 IS NULL
     AND 
     table4.col3 IS NULL
    )
)

EXPECTED DATA

col0 col1 col2 col3
2 NULL NULL NULL
4 NULL NULL val3
5 val1 NULL NULL
6 NULL val2 val3

Thank You!

2

Answers


  1. CREATE TABLE test (id INT, col1 CHAR(4), col2 CHAR(4), col3 CHAR(4));
    INSERT INTO test VALUES
    ( 1, 'v1', 'v2', 'v3'),
    ( 2, 'v1', 'v2', NULL),
    ( 3, 'v1', NULL, 'v3'),
    ( 4, 'v1', NULL, NULL),
    ( 5, NULL, 'v2', 'v3'),
    ( 6, NULL, 'v2', NULL),
    ( 7, NULL, NULL, 'v3'),
    ( 8, NULL, NULL, NULL),
    ( 9, 'v1', 'v2', 'v4'),
    (10, 'v1', NULL, 'v4'),
    (11, NULL, NULL, 'v4');
    
    SELECT *
    FROM test
    WHERE COALESCE(col1, 'v1') = 'v1'
      AND COALESCE(col2, 'v2') = 'v2'
      AND COALESCE(col3, 'v3') = 'v3';
    
    id col1 col2 col3
    1 v1 v2 v3
    2 v1 v2 null
    3 v1 null v3
    4 v1 null null
    5 null v2 v3
    6 null v2 null
    7 null null v3
    8 null null null

    fiddle

    But you must understand that this conditional expression is not SARGable, and it will cause full table scan which can be slow..

    Login or Signup to reply.
  2. Move the condition into the join:

    SELECT table4.col0, table4.col1, table4.col2, table4.col3
    FROM table4
    LEFT JOIN table1 ON table1.col1 = table4.col1
      AND table1.col1 = 'val1'
    LEFT JOIN table2 ON table2.col1 = table4.col2
      AND table2.col1 = 'val2'
    LEFT JOIN table3 ON table3.col1 = table4.col3
      AND table3.col1 = 'val3'
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search