🌟 Explained performand PostgreSQL solution 🚀 Beats 98%+ 🌠 without JOIN or IN or NOT IN
\n\n# Explain\n\n1. WITH red_company_id AS (...): This is a CTE that selects the com_id (company ID) from the Company table where the company\'s name is \'RED\'
![Proof of beats 98%+](https://assets.leetcode.com/users/images/b871563c-5d2d-4822-a74f-6a89c9fae488_1714213952.347768.png)\n\n# Explain\n\n1. `WITH red_company_id AS (...)`: This is a CTE that selects the `com_id` (company ID) from the `Company` table where the company\'s name is `\'RED\'`. The result of this CTE is a temporary result set that can be referenced within the subsequent `SELECT` statement.\n\n2. `SELECT name FROM SalesPerson AS s WHERE NOT EXISTS (...)`: This part of the query selects the name of salespeople from the `SalesPerson` table (aliased as `s`) who do not have any matching records in the `Orders` table for the company with the `com_id` found in the CTE.\n\n3. The `NOT EXISTS` subquery checks for the absence of orders made by each salesperson (`s.sales_id = o.sales_id`) for the `\'RED\'` company (`AND com_id = (SELECT com_id FROM red_company_id)`).\n\n\n## What is performand in this query\n\n\n- **Usage of CTE to avoid multiple execution**\n    - The use of a CTE for the company ID lookup is efficient if the name column in the Company table is indexed, as it will quickly find the ID for the `\'RED\'` company.\n\n- **Usage of NOT EXISTS**\n    - The `NOT EXISTS` clause is generally efficient for checking non-existence because it stops as soon as it finds a match, meaning it doesn\'t need to process all rows once it determines that a condition is met. In this case, it\'s used to ensure that a salesperson has no orders for the \'RED\' company.\n\n- **Not have any expensive clause**\n    - We do not have any expensive clause like `IN`, `JOIN`, `GROUP BY`, `ORDER BY`.\n\n- **Usage of = instead of IN clause**\n    - `=` Operator: This is used for equality comparison between a column and a single value. It is very efficient, especially if the column being compared is indexed. The database engine can quickly locate the row with the matching value using the index.\n\n\n# Code\n``` PostgreSQL []\nWITH red_company_id AS (\n    SELECT com_id\n    FROM Company\n    WHERE name = \'RED\'\n)\n\n\nSELECT name\nFROM SalesPerson AS s\nWHERE NOT EXISTS (\n    SELECT 1\n    FROM Orders AS o\n    WHERE s.sales_id = o.sales_id  \n            AND com_id = (SELECT com_id FROM red_company_id)\n)\n```\n``` MySQL []\nWITH red_company_id AS (\n    SELECT com_id\n    FROM Company\n    WHERE name = \'RED\'\n)\n\n\nSELECT name\nFROM SalesPerson AS s\nWHERE NOT EXISTS (\n    SELECT 1\n    FROM Orders AS o\n    WHERE s.sales_id = o.sales_id  \n            AND com_id = (SELECT com_id FROM red_company_id)\n)\n```\n``` Oracle []\nWITH red_company_id AS (\n    SELECT com_id\n    FROM Company\n    WHERE name = \'RED\'\n)\n\n\nSELECT name\nFROM SalesPerson s\nWHERE NOT EXISTS (\n    SELECT 1\n    FROM Orders o\n    WHERE s.sales_id = o.sales_id  \n            AND com_id = (SELECT com_id FROM red_company_id)\n)\n```\n\n____\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)
['MySQL', 'Oracle', 'PostgreSQL']