🌟 PostgreSQL solution with explain of performance issue
Two solutions\n\nWe have two solutions for this problem:\n\n\n## Store\'s columns are indexed :\n\nIf the table is large and each store\'s column is indexed :\n
# Two solutions\n\nWe have two solutions for this problem:\n\n\n## Store\'s columns are indexed :\n\nIf the table is large and each store\'s column is indexed :\n\n### Code\n```PostgreSQL []\nSELECT product_id,\n        \'store1\' AS store,\n        store1 AS price\nFROM Products\nWHERE store1 IS NOT NULL\nUNION\nSELECT product_id,\n        \'store2\' AS store,\n        store2 AS price\nFROM Products\nWHERE store2 IS NOT NULL\nUNION\nSELECT product_id,\n        \'store3\' AS store,\n        store3 AS price\nFROM Products\nWHERE store3 IS NOT NULL\n```\n```MySQL []\nSELECT product_id,\n        \'store1\' AS store,\n        store1 AS price\nFROM Products\nWHERE store1 IS NOT NULL\nUNION\nSELECT product_id,\n        \'store2\' AS store,\n        store2 AS price\nFROM Products\nWHERE store2 IS NOT NULL\nUNION\nSELECT product_id,\n        \'store3\' AS store,\n        store3 AS price\nFROM Products\nWHERE store3 IS NOT NULL\n```\n\n> The UNION approach can take advantage of these indexes.\n\n## Store\'s columns are not indexed :\n\nThis can be more efficient, especially if there are many stores, as it avoids multiple scans of the Products table.\n\n```PostgreSQL []\nSELECT\n  p.product_id,\n  s.store,\n  CASE s.store\n    WHEN \'store1\' THEN p.store1\n    WHEN \'store2\' THEN p.store2\n    WHEN \'store3\' THEN p.store3\n  END AS price\nFROM\n  Products p\nCROSS JOIN\n  (SELECT \'store1\' AS store\n   UNION ALL SELECT \'store2\'\n   UNION ALL SELECT \'store3\') s\nWHERE\n  (s.store = \'store1\' AND p.store1 IS NOT NULL)\n  OR (s.store = \'store2\' AND p.store2 IS NOT NULL)\n  OR (s.store = \'store3\' AND p.store3 IS NOT NULL)\n```\n```MySQL []\nSELECT\n  p.product_id,\n  s.store,\n  CASE s.store\n    WHEN \'store1\' THEN p.store1\n    WHEN \'store2\' THEN p.store2\n    WHEN \'store3\' THEN p.store3\n  END AS price\nFROM\n  Products p\nCROSS JOIN\n  (SELECT \'store1\' AS store\n   UNION ALL SELECT \'store2\'\n   UNION ALL SELECT \'store3\') s\nWHERE\n  (s.store = \'store1\' AND p.store1 IS NOT NULL)\n  OR (s.store = \'store2\' AND p.store2 IS NOT NULL)\n  OR (s.store = \'store3\' AND p.store3 IS NOT NULL)\n```\n\n____\n\n> The performance of SQL queries can depend on various factors, including the database system, the size of the data, the structure of the tables, and the presence of indexes. It\'s always a good idea to test different query strategies on your specific dataset to see which performs best.\n\nIf it was useful don\'t hesitate to upvote \uD83C\uDF20 Or keep it in your favorite \uD83C\uDF1F\n![Upvote invitation](https://github.com/MathieuSoysal/CROUS-assistant-Collector/assets/43273304/b801cf95-dcda-4733-ada2-939f5fcb29e0)
['PostgreSQL']