Aide-mémoire SQL vs NoSQL vs PostgreSQL vs Oracle DB

Comparaison approfondie SQL vs NoSQL : tous les types de jointure, CTE, sous-requêtes, fonctions de fenêtrage, transactions/isolation et JSON, côte à côte dans PostgreSQL, Oracle DB et MongoDB.

S’exécute dans votre navigateur · rien n’est envoyéNouveau

28 commandes

Concepts

Relational vs document data modelSQL databases (PostgreSQL, Oracle) store rows in tables with a fixed schema and relations enforced by foreign keys. MongoDB (a common NoSQL example) stores flexible JSON-like documents in collections, with no enforced schema and no joins between collections by default.

This is the real dividing line, more than SQL-vs-no-SQL as a language: relational systems are built around normalized tables and ACID transactions across them; document stores are built around denormalized documents that are each self-contained and fast to read as a whole.

Options

PostgreSQLTables with typed columns; a schema change needs ALTER TABLE. Supports a JSONB column type for flexible, semi-structured data within an otherwise relational table.
Oracle DBSame relational model as PostgreSQL; also supports JSON columns (JSON data type since 21c, with native storage).
MongoDB (NoSQL)Collections of BSON documents. Each document can have different fields; schema validation is optional and enforced per-collection, not database-wide.
When to choose whichA practical rule of thumb for picking between a relational database and a document store.

Relational databases fit data with clear structure and relationships that need consistency across tables (orders, invoices, inventory). Document databases fit data that is naturally self-contained and varies in shape between records (logs, user-generated content, product catalogs with wildly different attributes per category), or workloads that need horizontal write scaling across many servers more than strict cross-record consistency.

Options

Choose relational (PostgreSQL/Oracle) whenData has clear relationships, you need multi-table transactions, or reporting/ad-hoc querying across the data matters.
Choose document (MongoDB-style) whenRecords are naturally independent documents, the schema varies or evolves quickly, or you need to shard writes across many servers.

Query & read

Read rows / find documentsThe basic "give me matching records" operation.

Exemple

-- PostgreSQL / Oracle
SELECT name, email FROM users WHERE status = 'active';

// MongoDB
db.users.find({ status: "active" }, { name: 1, email: 1 });

Options

PostgreSQLSELECT <columns> FROM <table> WHERE <condition>;
Oracle DBIdentical SELECT syntax to PostgreSQL for a basic query.
MongoDB (NoSQL)db.<collection>.find(<filter-document>, <projection-document>). The second argument picks which fields to return, similar in purpose to the column list in SELECT.
Limit the number of rows returnedEvery system needs to cap how many rows/documents a query returns, but each one spells it differently; this is one of the most common porting gotchas.

Exemple

-- PostgreSQL (and the SQL:2008 standard)
SELECT * FROM products ORDER BY price LIMIT 10 OFFSET 20;

-- Oracle (12c+, SQL:2008 standard clause)
SELECT * FROM products ORDER BY price OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

-- Oracle (older style, pre-12c, or still common in existing code)
SELECT * FROM (SELECT p.*, ROWNUM rn FROM products p ORDER BY price) WHERE rn BETWEEN 21 AND 30;

// MongoDB
db.products.find().sort({ price: 1 }).skip(20).limit(10);

Options

PostgreSQLLIMIT <n> OFFSET <m>, or the standard FETCH FIRST <n> ROWS ONLY (both work).
Oracle DB (12c+)OFFSET <m> ROWS FETCH FIRST <n> ROWS ONLY is the modern form.
Oracle DB (pre-12c / legacy code)Wrap the query and filter on the ROWNUM pseudocolumn, since ROWNUM is assigned before ORDER BY unless you order in an inner query first.
MongoDB (NoSQL).skip(<m>).limit(<n>) chained onto a find() or sort().
Attention: Oracle's ROWNUM is assigned to rows as they are read, before any ORDER BY in the same query block. Filtering on ROWNUM directly with an ORDER BY in the same SELECT gives rows in the wrong order; always order in an inner query first, as shown above.
INNER JOIN — only matching rowsReturns only rows that have a match in both tables. The default join type if you just write JOIN.

Exemple

-- PostgreSQL / Oracle
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

// MongoDB
db.orders.aggregate([
  { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } },
  { $unwind: "$customer" } // drops orders with no matching customer, mimicking an inner join
]);

Options

PostgreSQL / Oracle DBINNER JOIN ... ON (or just JOIN). Identical syntax between the two.
MongoDB (NoSQL)$lookup always behaves like a left outer join (keeps every source document); adding $unwind on the joined array drops documents with no match, approximating an inner join.
LEFT (OUTER) JOIN — keep every row from the left tableReturns every row from the left table, with NULLs for columns from the right table when there is no match. The most common join for "show all X, with their Y if any".

Finding "rows with no match" (as in the example) is one of the most common uses of a LEFT JOIN: filter afterwards for NULL / an empty array on the right side.

Exemple

-- PostgreSQL / Oracle
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL; -- customers with no orders at all

// MongoDB
db.customers.aggregate([
  { $lookup: { from: "orders", localField: "_id", foreignField: "customerId", as: "orders" } },
  { $match: { orders: { $size: 0 } } }
]);

Options

PostgreSQL / Oracle DBLEFT JOIN or LEFT OUTER JOIN, both forms are accepted and identical.
MongoDB (NoSQL)$lookup already behaves like a left outer join by default (an empty array instead of NULLs when there is no match).
RIGHT JOIN and FULL (OUTER) JOINRIGHT JOIN is a LEFT JOIN with the tables reversed (every row from the right table kept instead). FULL JOIN keeps every row from both sides, matched where possible, NULL on the side with no match otherwise.

In practice, a RIGHT JOIN is rarely written on purpose: most people rewrite it as a LEFT JOIN with the table order swapped, since it reads more naturally ("all customers, with their orders" rather than "all orders, with their customers, from the right side").

Exemple

-- PostgreSQL / Oracle
SELECT c.name, o.id
FROM customers c
FULL JOIN orders o ON o.customer_id = c.id;

Options

PostgreSQL / Oracle DBRIGHT JOIN and FULL JOIN (or FULL OUTER JOIN) are both supported identically.
MongoDB (NoSQL)No direct equivalent to a RIGHT or FULL join in a single stage; approximated by running $lookup from each side and merging with $unionWith, which is noticeably more involved than the SQL form.
Self join — relate a table to itselfJoin a table to itself, typically to compare rows or follow a hierarchical reference like a manager/employee relationship.

Requires table aliases (here e and m) since the same table appears twice; without aliases the column references would be ambiguous.

Exemple

-- PostgreSQL / Oracle
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
WITH ... AS (...) — common table expressionsName a subquery and reference it like a temporary table within the rest of the query, making complex queries easier to read than deeply nested subqueries.

Exemple

-- PostgreSQL / Oracle
WITH big_customers AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 10000
)
SELECT c.name, b.spent
FROM big_customers b
JOIN customers c ON c.id = b.customer_id
ORDER BY b.spent DESC;

Options

PostgreSQL / Oracle DBWITH <name> AS (<query>), identical syntax. PostgreSQL also supports WITH RECURSIVE for hierarchical queries (for example an org chart); Oracle uses a different recursive CTE syntax with a defined cycle clause.
MongoDB (NoSQL)No direct equivalent; a multi-stage aggregation pipeline serves a similar organizational purpose, with each $group/$match/$project stage playing the role a CTE would in SQL.
Subquery — a query nested inside anotherA query used as a value, a row source, or a filter condition inside another query.

A CTE (WITH ...) is often clearer than a subquery for anything beyond a simple filter, and in PostgreSQL/Oracle the query planner generally treats them equivalently for performance purposes.

Exemple

-- PostgreSQL / Oracle
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

// MongoDB: usually restructured as an aggregation pipeline instead of a literal subquery
db.orders.aggregate([
  { $match: { total: { $gt: 1000 } } },
  { $group: { _id: "$customerId" } }
]);
Window functions: ROW_NUMBER, RANK, LAG/LEAD, running totalsCompute a value across a set of related rows (a "window") without collapsing them into a single group, unlike GROUP BY. The window is defined with OVER (PARTITION BY ... ORDER BY ...).

PostgreSQL and Oracle share essentially identical window function syntax, since both implement the SQL:2003 standard closely. This is one of the more portable advanced features between the two.

Exemple

-- PostgreSQL / Oracle (identical syntax)
SELECT
  customer_id,
  order_date,
  total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq,
  SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
  LAG(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_total
FROM orders;

Options

ROW_NUMBER()A unique, gapless sequential number per partition. Ties are broken arbitrarily by the ORDER BY tiebreaker (or row order if none).
RANK()Like ROW_NUMBER, but ties get the same rank, and the next rank skips numbers accordingly (1, 2, 2, 4).
DENSE_RANK()Like RANK, but without gaps after ties (1, 2, 2, 3).
LAG(col) / LEAD(col)The value of a column from the previous/next row in the partition, useful for period-over-period comparisons.
SUM(col) OVER (...)A running total (or AVG/MIN/MAX/COUNT) instead of a single aggregated value per group.
MongoDB equivalents of window-function-style analysisMongoDB has no OVER clause; the closest equivalents are $setWindowFields (ranking/running totals) and $facet (several independent aggregation branches in one query).

$setWindowFields was added specifically to close this gap with SQL window functions; it supports $rank, $denseRank, $documentNumber (ROW_NUMBER equivalent), $sum, $avg and more over a defined window.

Exemple

// MongoDB: a running total equivalent
db.orders.aggregate([
  { $setWindowFields: {
      partitionBy: "$customerId",
      sortBy: { orderDate: 1 },
      output: { runningTotal: { $sum: "$total", window: { documents: ["unbounded", "current"] } } }
  }}
]);
Group and aggregate dataComputing counts, sums or averages per group.

Exemple

-- PostgreSQL / Oracle
SELECT customer_id, COUNT(*), SUM(total)
FROM orders
GROUP BY customer_id;

// MongoDB
db.orders.aggregate([
  { $group: { _id: "$customerId", count: { $sum: 1 }, total: { $sum: "$total" } } }
]);

Options

PostgreSQL / Oracle DBGROUP BY <column> with aggregate functions (COUNT, SUM, AVG, MIN, MAX) in the SELECT list.
MongoDB (NoSQL)An aggregation pipeline: an array of stages, typically starting with $match (like WHERE) then $group (like GROUP BY).

Write

Insert a new row / documentAdding a new record.

Exemple

-- PostgreSQL / Oracle
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

// MongoDB
db.users.insertOne({ name: "Alice", email: "[email protected]" });

Options

PostgreSQL / Oracle DBINSERT INTO <table> (<columns>) VALUES (<values>);
MongoDB (NoSQL)db.<collection>.insertOne(<document>) for one document, insertMany([...]) for several.
Update existing rows / documentsChanging the value of a field on matching records.

Exemple

-- PostgreSQL / Oracle
UPDATE users SET status = 'inactive' WHERE last_login < '2026-01-01';

// MongoDB
db.users.updateMany(
  { lastLogin: { $lt: ISODate("2026-01-01") } },
  { $set: { status: "inactive" } }
);

Options

PostgreSQL / Oracle DBUPDATE <table> SET <column> = <value> WHERE <condition>; updates every matching row in one statement.
MongoDB (NoSQL)updateOne() changes the first match, updateMany() changes all matches. The second argument needs an update operator like $set; passing a plain document instead replaces the whole document.
Attention: An UPDATE or updateMany() without a WHERE/filter clause changes every row or document in the table/collection. Always double check the filter before running a bulk update, and consider a SELECT/find() with the same filter first to see what would be affected.
Delete rows / documentsRemoving records.

Exemple

-- PostgreSQL / Oracle
DELETE FROM sessions WHERE expires_at < NOW();

// MongoDB
db.sessions.deleteMany({ expiresAt: { $lt: new Date() } });

Options

PostgreSQL / Oracle DBDELETE FROM <table> WHERE <condition>;
MongoDB (NoSQL)deleteOne() / deleteMany(<filter>).
Attention: Just like UPDATE, a DELETE or deleteMany() without a filter removes every row or document. There is no undo once committed, so verify the filter against a SELECT/find() first.
Upsert (insert or update)Insert a record if it does not exist yet, otherwise update it. All three systems support this, with different syntax.

Exemple

-- PostgreSQL
INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

-- Oracle DB
MERGE INTO settings t
USING (SELECT 'theme' AS key, 'dark' AS value FROM dual) s
ON (t.key = s.key)
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (key, value) VALUES (s.key, s.value);

// MongoDB
db.settings.updateOne(
  { key: "theme" },
  { $set: { value: "dark" } },
  { upsert: true }
);

Options

PostgreSQLINSERT ... ON CONFLICT (<unique-column>) DO UPDATE SET .... Also supports a full MERGE statement (added in PostgreSQL 15) for more complex conditional logic.
Oracle DBMERGE INTO <table> USING <source> ON <match-condition> WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT .... More verbose, but handles multi-condition merges well.
MongoDB (NoSQL)Add { upsert: true } as an option to updateOne/updateMany/findOneAndUpdate.

Schema & keys

Define a table / schema validationRelational databases require a schema upfront. MongoDB does not require one, but can optionally enforce rules per collection.

Exemple

-- PostgreSQL
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Oracle DB
CREATE TABLE users (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR2(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

Options

PostgreSQLCREATE TABLE with typed columns; GENERATED ALWAYS AS IDENTITY for an auto-incrementing primary key (the modern replacement for the older SERIAL type).
Oracle DBSame CREATE TABLE structure; also supports GENERATED ALWAYS AS IDENTITY since 12c. VARCHAR2 instead of PostgreSQL's TEXT/VARCHAR, NUMBER instead of a specific integer type.
MongoDB (NoSQL)No CREATE TABLE needed; a collection is created on first insert. Optional $jsonSchema validation rules can be attached with db.createCollection() or collMod to reject documents that do not match a shape.
Auto-incrementing / unique ID generationBoth relational systems use a sequence under the hood; MongoDB generates a unique ID client-side by default.

Exemple

-- PostgreSQL: get the next value from a sequence directly
SELECT nextval('users_id_seq');

-- Oracle DB: sequences predate IDENTITY columns and are still common
CREATE SEQUENCE users_seq START WITH 1 INCREMENT BY 1;
INSERT INTO users (id, email) VALUES (users_seq.NEXTVAL, '[email protected]');

Options

PostgreSQLGENERATED ALWAYS AS IDENTITY columns use a sequence internally; nextval('<sequence>') reads the next value directly if needed.
Oracle DBCREATE SEQUENCE plus <sequence>.NEXTVAL in an INSERT is the classic pattern, still widely used alongside the newer IDENTITY column syntax.
MongoDB (NoSQL)Every document gets a 12-byte ObjectId in its _id field automatically unless you provide your own value; it encodes a timestamp plus random/counter bytes, not a simple incrementing integer.
Create an indexIndexes speed up lookups on a field, at the cost of extra storage and slightly slower writes, in every system.

Exemple

-- PostgreSQL / Oracle
CREATE INDEX idx_users_email ON users (email);

// MongoDB
db.users.createIndex({ email: 1 });

Options

PostgreSQL / Oracle DBCREATE INDEX <name> ON <table> (<column>); Oracle additionally auto-creates an index for primary keys and unique constraints, same as PostgreSQL.
MongoDB (NoSQL)db.<collection>.createIndex({ <field>: 1 }) (1 for ascending, -1 for descending). Compound and text indexes use the same method with more fields.

Transactions

Multi-statement transactionsBoth PostgreSQL and Oracle have always supported full ACID transactions across multiple statements and tables. MongoDB added multi-document ACID transactions later (version 4.0+ for replica sets, 4.2+ for sharded clusters) and they are more expensive to use there than in a relational database.

Exemple

-- PostgreSQL / Oracle
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

// MongoDB
const session = db.getMongo().startSession();
session.startTransaction();
try {
  accounts.updateOne({ _id: 1 }, { $inc: { balance: -100 } }, { session });
  accounts.updateOne({ _id: 2 }, { $inc: { balance: 100 } }, { session });
  session.commitTransaction();
} catch (e) {
  session.abortTransaction();
}

Options

PostgreSQLBEGIN / COMMIT / ROLLBACK, with full ACID guarantees across any number of tables, standard since always.
Oracle DBTransactions start implicitly with the first DML statement; COMMIT / ROLLBACK close them. SET TRANSACTION can tune isolation level.
MongoDB (NoSQL)Multi-document transactions need an explicit session and are commonly recommended to be kept short; a single-document update is already atomic without a transaction, which covers many common cases without the extra overhead.

Functions & syntax quirks

String concatenationA small but very common syntax difference that trips people up when porting queries between the two.

Exemple

-- PostgreSQL
SELECT first_name || ' ' || last_name FROM customers;

-- Oracle DB (same operator, also supports CONCAT for two arguments)
SELECT first_name || ' ' || last_name FROM customers;
SELECT CONCAT(first_name, last_name) FROM customers;

Options

PostgreSQL|| concatenates any number of operands, and CONCAT() is also available (and, unlike ||, treats NULL as an empty string instead of making the whole result NULL).
Oracle DBSame || operator; CONCAT() only accepts exactly two arguments, so chaining is needed for more.
MongoDB (NoSQL)$concat inside an aggregation expression: { $concat: ["$firstName", " ", "$lastName"] }.
Attention: In both PostgreSQL and Oracle, a || with a NULL operand makes the entire concatenated result NULL (except PostgreSQL's CONCAT() function, which treats NULL as empty). This is a common source of "why is this column blank" bugs.
Current date/timeGetting "now" uses different functions on each system.

Oracle has no standalone FROM-less SELECT: "SELECT SYSDATE;" is invalid, hence "FROM dual". PostgreSQL and MongoDB have no equivalent requirement.

Exemple

-- PostgreSQL
SELECT NOW(); -- timestamp with time zone
SELECT CURRENT_DATE; -- date only

-- Oracle DB
SELECT SYSDATE FROM dual; -- date, server time zone
SELECT SYSTIMESTAMP FROM dual; -- timestamp with time zone

Options

PostgreSQLNOW() or CURRENT_TIMESTAMP for the full timestamp, CURRENT_DATE for just the date.
Oracle DBSYSDATE for the current date/time (no fractional seconds or time zone), SYSTIMESTAMP for higher precision with time zone. Both need to be selected FROM dual, Oracle's one-row dummy table, when not selecting from a real table.
MongoDB (NoSQL)new Date() in a shell expression, or $$NOW inside an aggregation pipeline.
Parsing a string into a dateConverting a text value into a proper date/timestamp with an explicit format mask.

Both use TO_DATE with a format mask, but the mask tokens are not 100% identical between the two (for example Oracle is case-sensitive about MON vs Mon locale output in some NLS settings; PostgreSQL's format tokens come from its own to_char/to_date implementation, not Oracle's). Always test the exact format string on the target system rather than assuming it carries over unchanged.

Exemple

-- PostgreSQL
SELECT TO_DATE('2026-11-06', 'YYYY-MM-DD');

-- Oracle DB (same function name, slightly different format model in places)
SELECT TO_DATE('06-NOV-2026', 'DD-MON-YYYY');
CASE WHEN ... THEN ... ELSE ... ENDConditional logic inside a query, identical syntax in both relational systems.

Exemple

SELECT name,
  CASE
    WHEN total > 1000 THEN 'large'
    WHEN total > 100 THEN 'medium'
    ELSE 'small'
  END AS order_size
FROM orders;

Options

PostgreSQL / Oracle DBStandard CASE WHEN ... THEN ... [ELSE ...] END, fully portable between the two.
MongoDB (NoSQL)$switch (multiple branches, closest to CASE WHEN) or $cond (simple if/else) inside an aggregation expression.

Transactions & isolation

Transaction isolation levelsControl how much one transaction can see of another transaction's uncommitted or concurrently committed changes.

MongoDB (NoSQL) read concern levels ("local", "available", "majority", "linearizable", "snapshot") answer a related but not identical question: how durable/replicated a read result must be, rather than purely how isolated it is from other concurrent writes.

Exemple

-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Oracle DB
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Options

READ COMMITTEDThe default in both PostgreSQL and Oracle. Each statement sees only data committed before that statement began.
REPEATABLE READPostgreSQL: the whole transaction sees a consistent snapshot from its start. Oracle does not implement this level by name; its closest equivalent behavior comes from SERIALIZABLE.
SERIALIZABLEStrongest level: transactions behave as if run one at a time. Both systems implement this using optimistic concurrency, so a transaction can fail at commit time with a serialization error that the application must be ready to retry.
READ UNCOMMITTEDAllowed in the SQL standard, but PostgreSQL treats it identically to READ COMMITTED (it never implements true dirty reads); Oracle does not support it at all.
Reading a query execution planSee how the database actually plans to execute a query: which indexes it uses, join order, and estimated cost, before or after running it.

A "Seq Scan" (PostgreSQL) or "TABLE ACCESS FULL" (Oracle) on a large table where an index was expected is the most common thing to look for: it usually means a missing or unusable index for that query's filter.

Exemple

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Oracle DB
EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Options

PostgreSQLEXPLAIN shows the planned execution; EXPLAIN ANALYZE actually runs the query and shows real timing alongside the plan.
Oracle DBEXPLAIN PLAN FOR stores the plan in a plan table; DBMS_XPLAN.DISPLAY (or the older SET AUTOTRACE in SQL*Plus) formats it for reading.
MongoDB (NoSQL).explain("executionStats") appended to a query or aggregation shows which indexes were used (or a full collection scan) and timing per stage.

JSON in SQL

Store and query JSON within a relational tableBoth PostgreSQL and Oracle can store and query JSON documents inside an otherwise relational table, which blurs the line with a pure document store for many use cases.

Exemple

-- PostgreSQL
SELECT data->'address'->>'city' FROM customers WHERE data @> '{"active": true}';

-- Oracle DB (21c+)
SELECT JSON_VALUE(data, '$.address.city') FROM customers WHERE JSON_EXISTS(data, '$?(@.active == true)');

Options

PostgreSQLJSONB column type, with -> (returns JSON) and ->> (returns text) operators to navigate, and @> to test containment.
Oracle DBNative JSON column type (21c+); JSON_VALUE to extract a scalar, JSON_EXISTS to test a path condition.
MongoDB (NoSQL)Every document is already JSON-like (BSON) natively; no separate JSON column or extraction functions are needed since the whole document is queryable field by field.

Les descriptions de cette référence sont pour l’instant en anglais.

Explication

Recherchez ou filtrez par sujet. Chaque entrée montre le même concept côte à côte pour PostgreSQL, Oracle DB et un entrepôt de documents NoSQL typique (MongoDB), pour voir exactement en quoi la syntaxe diffère.

🔒 Votre saisie reste dans votre navigateur et n’est envoyée nulle part.

Questions fréquentes

Comment limiter le nombre de lignes dans Oracle par rapport à PostgreSQL ?

PostgreSQL et les versions récentes d’Oracle (12c+) prennent toutes deux en charge la clause standard OFFSET … FETCH FIRST n ROWS ONLY. L’ancien code Oracle utilise à la place la pseudo-colonne ROWNUM, attribuée avant ORDER BY, il faut donc trier dans une sous-requête au préalable.

MongoDB a-t-il un équivalent aux fonctions de fenêtrage SQL comme ROW_NUMBER ou un total cumulé ?

Oui, $setWindowFields, ajouté spécifiquement pour combler cet écart : il prend en charge $rank, $denseRank, $documentNumber et les sommes/moyennes cumulées sur une fenêtre définie, similaire à OVER (PARTITION BY ... ORDER BY ...) en SQL.

Et ensuite ?

Aussi utile

  • Formateur SQLFormatez des requêtes SQL pour MySQL, PostgreSQL, SQL Server, Oracle, SQLite et SQL standard. S’exécute dans votre navigateur.
  • Convertisseur CSV vers JSONConvertissez du CSV en JSON et du JSON en CSV. Prend en charge les champs entre guillemets et les séparateurs virgule, point-virgule ou tabulation.
  • Formateur JSONFormatez, validez et minifiez du JSON. Avec des messages d’erreur clairs. Le traitement reste dans votre navigateur.
  • Convertisseur YAML vers JSONConvertissez du YAML en JSON et du JSON en YAML. Pratique pour Kubernetes, Docker Compose, les pipelines CI et les configurations.

Un problème ou une suggestion ? Dites-le-nous