SQL Subqueries Explained: Types, Examples, and Best Practices

SQL Cheatsheet: The Ultimate Guide That Saved My Time and Effort SQL used to feel like a headache to me I’d spend hours debugging slow queries, wondering where I went wrong. But once I understood how to write efficient SQL queries, everything changed What used to take hours now gets done in minutes and you can do the same 𝗕𝗔𝗦𝗜𝗖 𝗖𝗢𝗠𝗠𝗔𝗡𝗗𝗦 ➛𝗧𝗵𝗲 𝗙𝗼𝘂𝗻𝗱𝗮𝘁𝗶𝗼𝗻 𝗬𝗼𝘂 𝗡𝗲𝗲𝗱 ↳ Retrieve Data with SELECT The SELECT command is the bread and butter of SQL. For example, to get the names of all employees, you can write: ➛SELECT name FROM employees ↳ Filter Results with WHERE Tired of manually searching through rows? The WHERE clause filters data For instance, to find all employees who work in the Sales department, use: ➛SELECT * FROM employees WHERE department = 'Sales' ↳ Insert Records with INSERT Adding new data to a table used to feel complicated, but INSERT made it straightforward To add a new employee named John, aged 30, write: ➛INSERT INTO employees (name, age) VALUES ('John', 30) ↳ Modify Data with UPDATE Made a mistake in your data? You can fix it without starting over If you want to change John’s age to 31, use: ➛UPDATE employees SET age = 31 WHERE name = 'John' ↳ Remove Records with DELETE Need to clean up your database? DELETE gets rid of unnecessary records quickly For example, to remove John from the employee list, write: ➛DELETE FROM employees WHERE name = 'John' 𝗔𝗗𝗩𝗔𝗡𝗖𝗘𝗗 𝗖𝗢𝗠𝗠𝗔𝗡𝗗𝗦 ➛𝗧𝗮𝗸𝗲 𝗬𝗼𝘂𝗿 𝗤𝘂𝗲𝗿𝗶𝗲𝘀 𝗧𝗼 𝗧𝗵𝗲 𝗡𝗲𝘅𝘁 𝗟𝗲𝘃𝗲𝗹 ↳ Combine Data with JOIN JOIN is a lifesaver when you need to work with multiple tables For example, to link customer orders with their details, you can write: ➛SELECT orders. id, customers. name FROM orders INNER JOIN customers ON orders.customer_id = customers. id ↳ Group Data with GROUP BY GROUP BY helps you analyze data by categories To count the number of employees in each department, write: ➛SELECT department, COUNT(*) FROM employees GROUP BY department ↳ Sort with ORDER BY Sorting data is a breeze with ORDER BY To find the highest earners by sorting salaries in descending order, use: ➛SELECT name, salary FROM employees ORDER BY salary DESC ↳ Filter Groups with HAVING When GROUP BY isn’t enough, HAVING steps in to filter groups. For instance, to identify departments with more than 5 employees, write: ➛SELECT depart, COUNT() FROM employees GROUP BY department HAVING COUNT() > 5 ↳ Use CTEs Break complex queries into manageable parts with CTEs ↳ Leverage Window Functions Perform calculations across rows while retaining the original data 𝗧𝗛𝗘 𝗕𝗜𝗚 𝗣𝗜𝗖𝗧𝗨𝗥𝗘 ➛𝗛𝗢𝗪 𝗜𝗧 𝗖𝗛𝗔𝗡𝗚𝗘𝗗 𝗠𝗬 𝗪𝗢𝗥𝗞 One of the key insights I learned was the SQL 𝐎𝐫𝐝𝐞𝐫 𝐨𝐟 𝐄𝐱𝐞𝐜𝐮𝐭𝐢𝐨𝐧, which completely changed how I approached query writing Understanding how SQL processes commands step by step allowed me to write better and faster queries 𝗡𝗢𝗪 𝗜𝗧’𝗦 𝗬𝗢𝗨𝗥 𝗧𝗨𝗥𝗡 ➛ Get the interview call: https://lnkd.in/ges-e-7J

💡 Database Journey for Every Data Professional and Data Teams Whether you’re a SQL Developer, Data Engineer, Data Analyst, Data Scientist, or Data Migration Specialist, this post can guide your path. 🔹 Start Simple Begin with one SQL tool—MySQL. Once you master it, you can easily learn other databases like PostgreSQL, Oracle, MSSQL, IBM DB2, depending on your project needs. Remember few sql scripts are differ in each Sql Databases if you compare one with other. 🔹 Expand to the Cloud Businesses today rely heavily on cloud databases. Some I’ve seen and used in projects include: GCP: Spanner, Cloud SQL AWS: Aurora, RDS SAS Databases (older data warehouse systems) These platforms are widely adopted and open up opportunities across industries. 👉 Call-to-Action: Which databases are you using right now? Share your thoughts—I’d love to learn from your experiences. #SQL #DataEngineering #DataScience #DatabaseManagement #CloudComputing #AWS #GCP #Oracle #PostgreSQL #MSSQL #IBMDB2 #Datamigration

Did standard SQL's English-like ordering accidentally force every parser into inefficiency for 50 years? That is one of the questions AUREON SQL answers. AUREON SQL is a next-generation dialect built around one principle: every object the parser constructs should be final. No placeholders. No throwaway intermediates. Here is what that looks like in the SELECT statement. 𝗥𝗲𝗹𝗮𝘁𝗶𝗼𝗻 𝗻𝗮𝘃𝗶𝗴𝗮𝘁𝗶𝗼𝗻 𝘄𝗶𝘁𝗵𝗼𝘂𝘁 𝗝𝗢𝗜𝗡 A colon operator replaces JOIN ... ON for every relationship type — parent-child, many-to-many, REPORT TO hierarchies, audit trail: FROM myschema.ORDER O, O:lines L, O:customer C, E:manager MGR 𝗜𝗻𝗹𝗶𝗻𝗲 𝗖𝗗𝗖 — 𝗵𝗶𝘀𝘁𝗼𝗿𝘆 𝗮𝘀 𝗮 𝗰𝗼𝗹𝘂𝗺𝗻 Historical values directly in SELECT — no subquery, no audit table: T.STATUS:1   -- most recent previous value T.STATUS:1:TS -- timestamp of that change T.STATUS:0   -- the very first value ever 𝗔𝗹𝗶𝗮𝘀𝗲𝘀 𝘂𝘀𝗮𝗯𝗹𝗲 𝗲𝘃𝗲𝗿𝘆𝘄𝗵𝗲𝗿𝗲 Name a column once in SELECT — use it in WHERE, GROUP BY, HAVING, ORDER BY, QUALIFY, and even in subsequent SELECT columns. No expression repetition. 𝗧𝘄𝗼 𝗪𝗛𝗘𝗥𝗘 𝘀𝘆𝗻𝘁𝗮𝘅𝗲𝘀, 𝗳𝗿𝗲𝗲𝗹𝘆 𝗺𝗶𝘅𝗲𝗱 Free-form SQL and the structured COL/VAL/COMP pattern can coexist in the same clause: WHERE O.AMOUNT > 100 AND IF(COL=O.REGION, VAL='EMEA', COMP=EQ) 𝗧𝗵𝗲 𝗙𝗥𝗢𝗠-𝗯𝗲𝗳𝗼𝗿𝗲-𝗦𝗘𝗟𝗘𝗖𝗧 𝗮𝗹𝘁𝗲𝗿𝗻𝗮𝘁𝗶𝘃𝗲 Standard SQL reads SELECT before FROM, so the parser cannot resolve column references until after the table is known. That forces two-phase resolution: allocate placeholders, then discard and replace them. For 25 column refs, up to 50 intermediate objects per query. AUREON SQL offers FROM first, then SELECT. The table is known before the column list is parsed — every reference resolves directly to a final object. At 50,000 queries/sec with 15 column refs average, that removes roughly 1.5 million intermediate allocations per second. Not reduced. Absent. The standard syntax remains fully valid. The alternative is there when you want the parser to have the table context first. SQL was conceived as a language business users could write themselves. AUREON SQL honours that — and asks: what would it look like if the parser's perspective also deserved to be served? #AUREONSQL #SQL #DatabaseDesign #QueryLanguage #DataEngineering

𝗦𝗤𝗟 𝗤𝘂𝗲𝗿𝘆 𝗘𝘅𝗲𝗰𝘂𝘁𝗶𝗼𝗻 𝗢𝗿𝗱𝗲𝗿 In SQL, queries are executed in a specific order, which can be quite different from the order in which the clauses are written. Here’s the logical order of SQL query execution: 1. FROM Specifies the tables from which to retrieve or manipulate data. 2. WHERE Filters rows based on specified conditions. Only rows that meet the conditions proceed to the next stage. 3. GROUP BY Groups rows into sets based on column(s) specified. Any aggregate functions (like SUM, COUNT, etc.) will now apply to each group. 4. HAVING Applies filters to groups created by GROUP BY. Only groups meeting these conditions move forward. 5. SELECT Determines which columns and expressions to return. Executes any functions or expressions listed in the SELECT clause. Deduplication of rows (DISTINCT) happens here if specified. 6. ORDER BY Sorts the result based on specified column(s) and sort direction (ASC or DESC). Does not impact the final rows selected, only the display order. 7. LIMIT  Restricts the number of rows returned by the query. Useful for pagination or getting a specific subset of rows. 𝗟𝗲𝘁𝘀 𝘂𝗻𝗱𝗲𝗿𝘀𝘁𝗮𝗻𝗱 𝘄𝗶𝘁𝗵 𝘁𝗵𝗶𝘀 𝘀𝗶𝗺𝗽𝗹𝗲 𝗦𝗤𝗟 𝗾𝘂𝗲𝗿𝘆  SELECT department, COUNT(employee_id) AS total_employees FROM employees WHERE status = 'active' GROUP BY department HAVING total_employees > 5 ORDER BY total_employees DESC LIMIT 10; 𝗧𝗵𝗶𝘀 𝗾𝘂𝗲𝗿𝘆 𝘄𝗼𝘂𝗹𝗱 𝗲𝘅𝗲𝗰𝘂𝘁𝗲 𝗶𝗻 𝘁𝗵𝗲 𝗳𝗼𝗹𝗹𝗼𝘄𝗶𝗻𝗴 𝗼𝗿𝗱𝗲𝗿: 1. FROM employees 2. WHERE status = 'active' 3. GROUP BY department 4. HAVING total_employees > 5 5. SELECT department, COUNT(employee_id) AS total_employees 6. ORDER BY total_employees DESC 7. LIMIT 10 This Ankit Bansal's YouTube video gives even more clarity on the SQL execution . SQL Order of Execution (Logical Explanation) | https://lnkd.in/gbdeWjFd Image credits : Nikki Siapno 🔷 𝗜𝗳 𝘆𝗼𝘂 𝘄𝗮𝗻𝘁 𝘁𝗼 𝗽𝗿𝗮𝗰𝘁𝗶𝗰𝗲 𝗠𝗼𝗿𝗲 𝗦𝗤𝗟 𝗿𝗲𝗮𝗹 𝘁𝗶𝗺𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀 ? 👉 Go through these Playlists : 1. SQL Tips and Tricks https://lnkd.in/gK_qsy2M 2. SQL Medium Complex Interview Problems https://lnkd.in/gj3pFXcP 3. LeetCode SQL Hard Problems https://lnkd.in/gHJdu5Cw 4. Complex SQL Questions for Interview Questions !! https://lnkd.in/g_4uyzHT ♻️ I share cloud , data analysis/data engineering tips, real world project breakdowns, and interview insights through my free newsletter. 🤝 Subscribe for free here → https://lnkd.in/ebGPbru9 ♻️ Repost to help others grow 🔔 Follow Abhisek Sahu for more #sql #engineering #dataanalysis #dataengineering #dataanalyst

Everyone says "learn SQL." Nobody gives you the actual cheat sheet. I analyzed 50+ analyst job descriptions and real queries from production dashboards. The same 10 functions appeared in almost every single one. Here they are. Syntax included. Copy and use today. 🟢 FILTERING ① WHERE SELECT * FROM orders WHERE status = 'completed' Filter rows before aggregation. This is your first instinct for any query. ② HAVING SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 100 Filter AFTER aggregation. WHERE filters rows. HAVING filters groups. Know the difference. ③ CASE WHEN SELECT order_id, CASE WHEN amount > 1000 THEN 'high' WHEN amount > 100 THEN 'medium' ELSE 'low' END AS tier FROM orders IF/ELSE logic inside SQL. Use it to create categories, flags, labels on the fly. 🟢 AGGREGATION ④ GROUP BY + COUNT / SUM / AVG SELECT region, COUNT(*) AS users, AVG(revenue) AS avg_rev FROM sales GROUP BY region The foundation of every report. Group rows → calculate metrics per group. ⑤ DISTINCT SELECT COUNT(DISTINCT customer_id) FROM orders Count unique values. Without DISTINCT you count duplicates and your numbers are wrong. 🟢 JOINS ⑥ LEFT JOIN SELECT o.order_id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.id Keep ALL rows from left table. Match what you can from right. Unmatched → NULL. This is 80% of all joins you will ever write. ⑦ INNER JOIN SELECT p.name, s.quantity FROM products p INNER JOIN stock s ON p.id = s.product_id Keep ONLY rows that match in BOTH tables. Use when you need strict matches with no NULLs. 🟢 WINDOW FUNCTIONS ⑧ ROW_NUMBER() SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders Number rows within each group. Filter WHERE rn = 1 to get the latest order per user. Use this daily. ⑨ SUM() OVER / AVG() OVER SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) AS running_total FROM daily_sales Running totals and moving averages without GROUP BY. Your row-level data stays intact. ⑩ LAG() / LEAD() SELECT date, revenue, LAG(revenue) OVER (ORDER BY date) AS prev_day FROM daily_sales Access the previous or next row's value. Calculate day-over-day change in one line. 📌 QUERY TEMPLATE (covers 90% of tasks): SELECT   dimension,   COUNT(*) AS cnt,   SUM(metric) AS total FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id WHERE a.date >= '2024-01-01' GROUP BY dimension HAVING COUNT(*) > 10 ORDER BY total DESC LIMIT 20 This single template handles: → Segmented reports → Top-N analysis → Filtered aggregations → Multi-table analytics The learning path: → Week 1: ①–③ (filtering) → Week 2: ④–⑤ (aggregation) → Week 3: ⑥–⑦ (joins) → Week 4: ⑧–⑩ (window functions) 4 weeks. 10 functions. 90% of analyst SQL covered. Save this cheat sheet. Share with someone starting their analytics journey. 👇 #sql #dataanalytics #analytics #cheatsheet #career #datascience #programming

7 SQL tricks I wish someone told me when I started as a Data Analyst 🧠 After 2+ years of writing SQL daily at work — these are the ones that actually changed how I work. Save this. You'll thank yourself later. 📌 1️⃣ Use CTEs instead of nested subqueries Nested subqueries look smart. They're a nightmare to debug. CTEs (WITH clause) break your logic into clean, readable steps. Your future self — and your teammates — will thank you. 2️⃣ ROW_NUMBER() is your best friend for duplicates Don't just do DISTINCT and hope for the best. Use ROW_NUMBER() with PARTITION BY to identify and remove duplicates with full control over which row you keep. 3️⃣ Always filter early, not late Put your WHERE clause conditions as early as possible — ideally inside your CTE or subquery. Filtering at the end means your query already processed millions of rows it didn't need to. 4️⃣ COALESCE() handles NULLs cleanly NULL values will silently break your aggregations. COALESCE(column, 0) or COALESCE(column, 'Unknown') replaces NULLs before they cause problems in your calculations. 5️⃣ Use EXPLAIN / Query Plan before running heavy queries Before running a query on a million-row table — check the execution plan first. It shows you exactly where the bottleneck is so you can fix it before it kills your database. 6️⃣ Window functions > GROUP BY for analytical work GROUP BY collapses your rows. Window functions like SUM() OVER(), AVG() OVER() let you aggregate AND keep every row. Perfect for running totals, rankings, and period-over-period comparisons. 7️⃣ Date functions are underrated — learn them deeply DATETRUNC(), DATEDIFF(), EXTRACT() — these will save you hours. Most reporting problems I've seen come down to someone not handling dates correctly. These aren't just theory — I use every single one of these in real projects. SQL isn't just about writing queries that run. It's about writing queries that are fast, readable, and actually correct. ✅ Which one of these did you not know? Drop it in the comments 👇 And if you have a SQL trick I missed — share it! Let's make this a thread. 🧵 💡 I'm Sohan — a Data Analyst with 2+ years of experience in SQL, Power BI & Python, documenting my journey into Data Engineering and AWS. 👉 Follow me for practical SQL tips, Power BI insights, and real talk about the data world — posted regularly. 🚀 #SQL #SQLTips #DataAnalytics #DataAnalyst #DataEngineering #PowerBI #Python #LearningInPublic #CareerGrowth #DataSkills

SQL doesn’t have a fixed number of “queries” or “joins”—it’s a language, not a limited set of commands. But you can group things in a way that’s easy to understand for interviews and real work. 🔹 1. Types of SQL Queries 🟢 Main Categories (5 types) 1. DQL (Data Query Language)** * Used to fetch data * Example: ```sql SELECT * FROM employees; ``` 2. DML (Data Manipulation Language)** * Used to modify data * Commands: ```sql INSERT INTO employees VALUES (1, 'John'); UPDATE employees SET name='Sam' WHERE id=1; DELETE FROM employees WHERE id=1; ```3. DDL (Data Definition Language)** * Used to define structure ```sql CREATE TABLE employees (id INT, name VARCHAR(50)); ALTER TABLE employees ADD salary INT; DROP TABLE employees; ```4. DCL (Data Control Language)** * Permissions ```sql GRANT SELECT ON employees TO user1; REVOKE SELECT ON employees FROM user1; ``` **5. TCL (Transaction Control Language)** * Manage transactions ```sql COMMIT; ROLLBACK; SAVEPOINT sp1; 👉 So broadly, **5 types of SQL queries** 🔹 2. Types of SQL Joins 🟢 Main Joins (5 types) 1. INNER JOIN 👉 Returns only matching records from both tables ```sql SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.id; ``` 2. LEFT JOIN (LEFT OUTER JOIN) 👉 Returns all records from left table + matched from right ```sql SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id; ``` 3. RIGHT JOIN (RIGHT OUTER JOIN) 👉 Returns all records from right table + matched from left ```sql SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id; ``` 4. FULL JOIN (FULL OUTER JOIN) 👉 Returns all records from both tables ```sql SELECT e.name, d.dept_name FROM employees e FULL JOIN departments d ON e.dept_id = d.id; ``` 5. CROSS JOIN 👉 Returns all combinations (Cartesian product) ```sql SELECT e.name, d.dept_name FROM employees e CROSS JOIN departments d; `` 🔹 3. Visual Understanding | Join Type | Result | | ---------- | ------------------------- | | INNER JOIN | Matching data only | | LEFT JOIN | All left + matching right | | RIGHT JOIN | All right + matching left | | FULL JOIN | Everything | | CROSS JOIN | All combinations | 🔹 4. Real-Time Example (Interview Style) Tables: Employees | id | name | dept_id | | -- | ---- | ------- | **Departments** | id | dept_name | Scenario: 👉 “Show all employees even if department is missing” ✔ Use: ```sql LEFT JOIN 👉 “Show only employees with valid departments” ✔ Use: ```sql INNER JOIN ``` 🔹 5. Key Tips (Very Important) * Always use **JOIN with ON condition** * Use aliases (`e`, `d`) for readability * Avoid **CROSS JOIN** in real projects (huge data) * LEFT JOIN is most used in real-time projects #SQL #StructuredQueryLanguage #DataAnalytics #DataScience #Database #TechLearning #Programming #Coding #LearnSQL #ITJobs

⚠️ DAY 13/15 — SQL TRAP: PARTITION BY vs GROUP BY GROUP BY collapses your data. PARTITION BY keeps everything. Most people don't know the difference. 👇 🎯 The Situation: You want to show each employee's name, their salary AND their department's total salary — all in the same row. You try GROUP BY. Result → only 2 rows. 😵 All employee names and individual salaries — GONE. Just department totals left. But you needed BOTH individual details AND group totals together! 🧠 Here's the simple difference: GROUP BY → collapses all rows into groups. You lose individual employee details forever. 5 employees become 2 department rows. PARTITION BY → calculates the group total BUT keeps every row intact. 5 employees stay as 5 rows. Each row also shows their department total. Same calculation. Completely different output. ✅ The Result Difference: GROUP BY result → Engineering = 24000 Marketing = 11000 (Only 2 rows. Names gone.) ❌ PARTITION BY result → Alice → 9000 → dept total 24000 ✅ Bob → 7000 → dept total 24000 ✅ Carol → 6000 → dept total 11000 ✅ Dave → 5000 → dept total 11000 ✅ Eve → 8000 → dept total 24000 ✅ All 5 rows. Individual salaries. Department totals. Everything together. 💡 Real Life Example: Imagine a school report. GROUP BY = show only class average. Individual student marks disappear. 😵 PARTITION BY = show every student's mark AND their class average side by side. ✅ One gives you a summary. Other gives you full detail with context. 📌 Save This Rule: → Need only group totals? → GROUP BY → Need individual rows AND group totals together? → PARTITION BY → PARTITION BY always uses OVER() → SUM(salary) OVER(PARTITION BY department) → GROUP BY collapses rows → you lose individual details → PARTITION BY never collapses → all rows always stay 🔑 One Line to Remember: GROUP BY = collapses rows = summary only PARTITION BY = keeps all rows = detail + summary together This is called a Window Function. One of the most powerful and most feared SQL concepts in interviews. 😎 💬 Real Talk: Window functions like PARTITION BY separate beginner SQL from advanced SQL. If you understand this — you're already ahead of most candidates in interviews. 🙋 Quick Quiz: If you use PARTITION BY department — how many rows will you get for a table with 10 employees across 3 departments? Drop your answer below 👇 Follow for Day 14 tomorrow — almost at the finish line! 🚀 #SQL #SQLForBeginners #WindowFunctions #DataAnalytics #LearnSQL #SQLTips #DataEngineering #InterviewPrep

SQL: The Complete Reference - https://lnkd.in/eaz53F7i Look for "Read and Download Links" section to download. Follow me if you like this post. #SQL #Databases #DataAnalysis #DataScience #LLMs #GenAI #GenerativeAI