O SELECT é o comando que recupera os dados gravados nas tabelas. Pode ser um comando simples e pequeno, mas também pode ser complexo e extenso.

Devido à complexidade da instrução SELECT, os elementos e argumentos de sintaxe detalhados não são mostrados aqui. Para entendermos melhor o SELECT, vamos primeiramente, identificar seu processamento Lógico(que não é a mesma da construção da query):

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Agora que sabemos como é a ordem da leitura do comando, vamos ao exemplos.

Para iniciarmos, temos três(3) exemplos de código.
– 1. Retornando todas as linhas e todas as colunas da tabela (com o uso do *).
– 2. Usando a definição de alias da tabela para obter o mesmo resultado.
– 3. Retornando todas as linhas da tabela ALUNO com todas as colunas inseridas manualmente e empregando alias para colunas e tabelas.
Não usem o * em ambiente de produção, existe algumas matérias de tunning, segurança, disponibilidade, etc, que explicam o motivo. Um outro post eu descrevo os motivos.

--1.
SELECT * FROM ALUNO

--2.
SELECT a.* FROM ALUNO AS a

--3.
SELECT a.IDALUNO AS 'ID'
	, a.NOME AS 'PRIMEIRO NOME'
	, a.SEXO
	, a.NASCIMENTO
	, a.EMAIL
FROM ALUNO AS a

Todos os resultados nesse caso será exatamente o mesmo, salvo a consulta 3, que tem o apelido (alias) para a coluna IDALUNO e NOME.

Seguindo em frente, vemos mais duas cláusulas, WHERE e ORDER BY.
A Cláusula WHERE vai criar um filtro para o select onde vai trazer somente os resultados que SEXO for igual a M e a cláusula ORDER BY vai ordenar o resultado da sua consulta pela coluna NOME sentido decrescente, caso queira em ordem crescente substitua o DESC por ASC ou não coloque nada, por default, ele já é ascendente.

SELECT a.IDALUNO AS 'ID'
	, a.NOME
	, a.SEXO
	, a.NASCIMENTO
	, a.EMAIL
FROM ALUNO AS a
WHERE SEXO = 'M'
ORDER BY NOME DESC

Na query abaixo vemos a cláusula COUNT() e GROUP BY. Usamos o COUNT() quando queremos contar determinada informação, nesse caso vamos contar os TIPOS de telefones da nossa tabela TELEFONE, observe que usamos o GROUP BY para agrupar o campo que desejamos contar e usamos o ORDER BY para classificar primeiramente o resultado de forma ascendente na coluna QTD que está contando o resultado e de forma descendente na coluna TIPO.

SELECT COUNT(TIPO) AS 'QTD'
	, TIPO
FROM TELEFONE
GROUP BY TIPO
ORDER BY QTD, TIPO DESC

É muito comum, termos que fazer um select com dados de mais de uma tabela e para realizar consulta desse tipo é usado a cláusula JOIN. A Cláusula JOIN permite que dados de uma tabela seja combinados com dados de outras tabelas. Essas tabelas podem se relacionar em uma consulta por dois modos:
1. Especificando a coluna de cada tabela a ser usada para a junção, os relacionamentos (PK e FK) ou;
2. Especificando um operador lógico (por exemplo, = ou <>) a ser usado na comparação de valores das colunas.

Os tipos de associações com JOIN são:

INNER JOIN – usada para obter dados relacionados entre duas tabelas. Para especificar o tipo de associação, é usado a cláusula ON, observe a sintaxe:

SELECT A.NOME
	, E.BAIRRO
	, E.UF
FROM ALUNO AS A
	INNER JOIN ENDERECO AS E ON A.IDALUNO = E.ID_ALUNO
GO

LEFT JOIN ou LEFT OUTER JOIN – obtém não somente os dados relacionados de duas tabelas, mas também os dados não relacionados na tabela que encontra-se a esquerda da cláusula JOIN.

Quando encontrados valores na tabela a esquerda que não possui relações na tabela a direita do JOIN, os campos serão apresentados como NULL.

SELECT A.NOME
	, T.TIPO
	, T.NUMERO
FROM ALUNO AS A
	LEFT JOIN TELEFONE AS T ON A.IDALUNO = T.ID_ALUNO
GO

RIGHT JOIN ou RIGHT OUTER JOIN
Parecido com o LEFT JOIN, ele busca todos os registros da tabela da direita independente de ter dados relacionados na tabela da esquerda, as linhas sem relações na tabela da esquerda é dado os campos como NULL

FULL JOIN ou FULL OUTER JOIN
O FULL JOIN é a junção dos RIGTH e LEFT JOIN, ele trás pelo menos uma vez cada linha das duas tabelas, a linha que não tiver registro relacionado na outra tabela, o campo é deixado como NULL

CROSS JOIN ou CROSS OUTER JOIN

No CROSS JOIN é feito um plano cartesiano das duas tabelas. Ou seja, todas as linhas de uma tabela são cruzadas com todas as linhas da outra tabela.

Um bom exemplo do seu uso é as chave de grupos de uma Copa do Mundo, onde todos os times se enfrentam.

Usando mais de um INNER JOIN na query

Observe abaixo o uso de alias para as tabelas e assim podermos selecionar duas colunas com nomes iguais em tabelas diferentes (colunas ambíguas), no nosso exemplo as colunas de relacionamento ou comparação ID_ALUNO.

SELECT A.NOME
	, T.TIPO
	, T.NUMERO
	, E.BAIRRO
	, E.UF
FROM ALUNO AS A
	INNER JOIN TELEFONE AS T ON A.IDALUNO = T.ID_ALUNO
	INNER JOIN ENDERECO AS E ON A.IDALUNO = E.ID_ALUNO
GO

Usando mais de um JOIN diferente na query

SELECT A.NOME
	, T.TIPO
	, T.NUMERO
	, E.BAIRRO
	, E.UF
FROM ALUNO AS A
	LEFT JOIN TELEFONE AS T ON A.IDALUNO = T.ID_ALUNO
	INNER JOIN ENDERECO AS E ON A.IDALUNO = E.ID_ALUNO
GO

Representação gráfica dos JOINS

Comando JOIN SQL: EXEMPLOS + Diagrama Ilustrado

E pra encerrar o Post de hoje, uma amostra sobre como trabalhar com dados NULLs

SELECT A.NOME,
	ISNULL(T.TIPO, 'SEM') AS "TIPO",
	ISNULL(T.NUMERO,'NUMERO') AS "TELEFONE",
	E.BAIRRO,
	E.UF
FROM ALUNO A 
	LEFT JOIN TELEFONE T ON A.IDALUNO = T.ID_ALUNO
	INNER JOIN ENDERECO E ON A.IDALUNO = E.ID_ALUNO
GO

Documentação Oficial:
https://docs.microsoft.com/pt-br/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15
Consulte também
https://docs.microsoft.com/pt-br/sql/t-sql/queries/select-examples-transact-sql?view=sql-server-ver15