Postgres - Consulta

• Introducció
• where
• group by
• join

Introducció

Una consulta és una funció que es composa utilitzant 6 funcions que s’han d’escriure en un ordre concret, i que retorna 1 taula com a resultat.

Tingues en compte que només el select és obligatori, i que encara que s’escrigui primer, s’executa l’últim

Per tant, quan comencem a crear una consulta començarem sempre pel from, després pel where, etc., seguint l’ordre d’execució.

Hem d’afegir select * perquè és obligatori que una consulta comenci per select:

select *
from departments join employees using(deparment_id)
where deparment_id = 100

Si utilitzem la funció group by, per seleccionar les columnes agrupades abans hem de transformar els grups en files utilitzant funcions d’agregació:

select deparment_id, count(*)
from departments natural join employees
group by deparment_id

A Beginner’s Guide to the True Order of SQL Operations. The SQL language is very intuitive. Until it isn’t. Over the years, a lot of people have criticised the SQL language for a variety of reasons because the lexical order of operations does not match the logical order of operations. We humans may sometimes (often) intuitively understand this ordering difference.

where

What Is the SQL WHERE Clause?. The WHERE clause is a fundamental part of SQL. This clause filters results and applies conditions to SQL queries. It is most commonly used alongside the SELECT statement.

How to Write a WHERE Clause in SQL. We explain how to use the SQL WHERE clause with practical examples. If you have just started learning SQL and want to know how to retrieve or work with only a specific portion of the data stored in your tables, then this article is for you!

Using AND, OR, and NOT Operators in SQL AND, OR, and NOT are important logical operators in SQL. They help you combine the conditions used to filter records. They are most commonly used in conjunction with the WHERE or HAVING clauses.

group by

join

7 SQL JOIN Examples With Detailed Explanations Do you need to join several tables to get the necessary result set? The SQL JOIN is a basic yet important tool used by data analysts working with relational databases. And I understand it can be difficult to choose from the zillions of introductory guides to joins. In this article, I will focus on real-world examples with detailed explanations.

An Illustrated Guide to the SQL CROSS JOIN. What is an SQL CROSS JOIN statement? When should you use it? When shouldn’t you use it? This post will tell you what you need to know about CROSS JOIN.

An Illustrated Guide to the SQL INNER JOIN What is an SQL INNER JOIN, and how does it work? Let’s find out!

An Illustrated Guide to the SQL Self Join What is an SQL self join and how does it work? When should it be used? We’ll provide answers to those questions!

How to Join Two Tables in SQL. Querying data from multiple tables is very common when working with relational databases. It is not difficult if you know how to use the dedicated SQL operators for doing this. In this article, you will learn how to join two tables by using WHERE and by using a special operator JOIN, and you will see how to filter rows in the result set.

Learning JOINs With Real World SQL Examples. The JOIN statement lets you work with data stored in multiple tables. In this article, I’ll walk you through the topic of JOIN clauses using real world SQL examples.

How Do You Get Rid of Duplicates in an SQL JOIN?. Do you have unwanted duplicates from your SQL JOIN query? In this article, I’ll discuss the possible reasons for getting duplicates after joining tables in SQL and show how to fix a query depending on the reason behind the duplicates.

What Is the Difference Between WHERE and ON in SQL JOINs. When you join tables in SQL, you may have conditions in an ON clause and in a WHERE clause. Many get confused by the difference between them. In this article, we will discuss this topic by first reminding you the purpose of the ON and WHERE clauses then by demonstrating with examples which types of conditions should be in each of these clauses.