> For the complete documentation index, see [llms.txt](https://educacion.gitbook.io/programacion/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://educacion.gitbook.io/programacion/ud8-persistencia-en-bases-de-datos/jdbc/consultas-avanzadas.md).

# Consultas avanzadas

JDBC vai a permitir o emprego de operadores SQL para consultas mais complexas

## JOIN, LEFT JOIN e RIGHT JOIN

JDBC vai a permitir embeber nas consultas os diferentes operadores de union de taboas:

```java
String sql = "SELECT a.* FROM alumnos a " +
                "JOIN matricula m ON a.id = m.id_alumno " +
                "JOIN asignaturas asig ON m.id_asignatura = asig.id " +
                "WHERE asig.nombre LIKE ?";
try (Connection con = DriverManager.getConnection(Lector.getInstancia().getServidor(), Lector.getInstancia().getUsuario(), Lector.getInstancia().getContraseña());
             PreparedStatement ps = con.prepareStatement(sql);){
            ps.setString(1,"%"+nome+"%");
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                Alumno alumno = new Alumno(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getDate(4).toLocalDate()
                );
                lista.add(alumno);
            }

        } catch (SQLException e){
            System.out.println(e.getMessage());
        }
        return lista;     
```

## GROUP BY e clausulas HAVING

Imos a poder definir consultas agrupando taboas e aplicando opcións de agrupado no filtrado.

```java
String sql = "SELECT * FROM alumnos a " +
                "JOIN matricula m ON a.id = m.id_alumno " +
                "GROUP BY a.id " +
                "HAVING COUNT(m.id_asignatura) > ?";
```

## SUBCONSULTAS

E posible escribir subconsultas embebidas empregando JDBC

```java
String sql = "SELECT * FROM alumnos a " +
                "WHERE id NOT IN ( " +
                "SELECT DISTINCT id_alumno " +
                "FROM matricula )";
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://educacion.gitbook.io/programacion/ud8-persistencia-en-bases-de-datos/jdbc/consultas-avanzadas.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.