github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/select/order_by_with_nulls.result (about) 1 DROP TABLE IF EXISTS t1; 2 CREATE TABLE t1 ( 3 id INT AUTO_INCREMENT, 4 name VARCHAR(255), 5 category_id INT, 6 d TIMESTAMP, 7 PRIMARY KEY (id) 8 ); 9 INSERT INTO t1 (id, name, category_id, d) VALUES 10 (1, 'aaa', 1, '2010-06-10 19:14:37'), 11 (2, 'bbb', 2, '2010-06-10 19:14:55'), 12 (3, 'ccc', 1, '2010-06-10 19:16:02'), 13 (4, 'ddd', 1, '2010-06-10 19:16:15'), 14 (5, 'eee', 2, '2010-06-10 19:16:35'); 15 SELECT * FROM t1 ORDER BY d; 16 id name category_id d 17 1 aaa 1 2010-06-10 19:14:37 18 2 bbb 2 2010-06-10 19:14:55 19 3 ccc 1 2010-06-10 19:16:02 20 4 ddd 1 2010-06-10 19:16:15 21 5 eee 2 2010-06-10 19:16:35 22 SELECT * FROM (SELECT * FROM t1 ORDER BY d DESC) temp ORDER BY d DESC; 23 id name category_id d 24 5 eee 2 2010-06-10 19:16:35 25 4 ddd 1 2010-06-10 19:16:15 26 3 ccc 1 2010-06-10 19:16:02 27 2 bbb 2 2010-06-10 19:14:55 28 1 aaa 1 2010-06-10 19:14:37 29 SELECT * FROM t1 WHERE category_id = 1 ORDER BY id; 30 id name category_id d 31 1 aaa 1 2010-06-10 19:14:37 32 3 ccc 1 2010-06-10 19:16:02 33 4 ddd 1 2010-06-10 19:16:15 34 SELECT * FROM t1 WHERE category_id = 1 ORDER BY d; 35 id name category_id d 36 1 aaa 1 2010-06-10 19:14:37 37 3 ccc 1 2010-06-10 19:16:02 38 4 ddd 1 2010-06-10 19:16:15 39 SELECT * FROM t1 WHERE category_id = 1 ORDER BY 1; 40 id name category_id d 41 1 aaa 1 2010-06-10 19:14:37 42 3 ccc 1 2010-06-10 19:16:02 43 4 ddd 1 2010-06-10 19:16:15 44 SELECT * FROM t1 WHERE category_id = 1 ORDER BY 1+1; 45 id name category_id d 46 1 aaa 1 2010-06-10 19:14:37 47 3 ccc 1 2010-06-10 19:16:02 48 4 ddd 1 2010-06-10 19:16:15 49 SELECT * FROM t1 WHERE category_id = 1 ORDER BY SIN(1); 50 id name category_id d 51 1 aaa 1 2010-06-10 19:14:37 52 3 ccc 1 2010-06-10 19:16:02 53 4 ddd 1 2010-06-10 19:16:15 54 SELECT * FROM t1 WHERE category_id = 1 ORDER BY TRUE; 55 SQL syntax error: non-integer constant in ORDER BY 56 DROP TABLE IF EXISTS t1; 57 CREATE TABLE t1 ( 58 name VARCHAR(200), 59 area CHAR(200), 60 PRIMARY KEY (name) 61 ); 62 INSERT INTO t1() VALUES 63 ('a','b'), 64 ('tewr','lojj'), 65 ('The index may also be used even if the ORDER BY d', ' all unused portions of the index and all ex'), 66 ('ssed by the query, the index is used', 'ex is more efficient than a table scan if c'), 67 ('more expensive than scanning the table and', 'e optimizer probably does not use the index. If SELECT'), 68 ('imilar to the same queries without DESC', 'homogeneity, but need not have the same actual direction.'); 69 SELECT name FROM t1 ORDER BY name; 70 name 71 The index may also be used even if the ORDER BY d 72 a 73 imilar to the same queries without DESC 74 more expensive than scanning the table and 75 ssed by the query, the index is used 76 tewr 77 SELECT name FROM t1 ORDER BY name,area; 78 name 79 The index may also be used even if the ORDER BY d 80 a 81 imilar to the same queries without DESC 82 more expensive than scanning the table and 83 ssed by the query, the index is used 84 tewr 85 SELECT name FROM t1 ORDER BY area,name; 86 name 87 The index may also be used even if the ORDER BY d 88 a 89 more expensive than scanning the table and 90 ssed by the query, the index is used 91 imilar to the same queries without DESC 92 tewr 93 SELECT * FROM t1 WHERE name = 'a' ORDER BY area; 94 name area 95 a b 96 SELECT * FROM t1 WHERE LENGTH(name) > 10 ORDER BY name ASC; 97 name area 98 The index may also be used even if the ORDER BY d all unused portions of the index and all ex 99 imilar to the same queries without DESC homogeneity, but need not have the same actual direction. 100 more expensive than scanning the table and e optimizer probably does not use the index. If SELECT 101 ssed by the query, the index is used ex is more efficient than a table scan if c 102 SELECT * FROM t1 WHERE LENGTH(name) < 10 ORDER BY name DESC; 103 name area 104 tewr lojj 105 a b 106 SELECT * FROM t1 WHERE LENGTH(name) > 10 ORDER BY LENGTH(area); 107 name area 108 ssed by the query, the index is used ex is more efficient than a table scan if c 109 The index may also be used even if the ORDER BY d all unused portions of the index and all ex 110 more expensive than scanning the table and e optimizer probably does not use the index. If SELECT 111 imilar to the same queries without DESC homogeneity, but need not have the same actual direction. 112 SELECT name FROM t1 ORDER BY name ASC, area DESC; 113 name 114 The index may also be used even if the ORDER BY d 115 a 116 imilar to the same queries without DESC 117 more expensive than scanning the table and 118 ssed by the query, the index is used 119 tewr 120 SELECT name FROM t1 ORDER BY name DESC, area ASC; 121 name 122 tewr 123 ssed by the query, the index is used 124 more expensive than scanning the table and 125 imilar to the same queries without DESC 126 a 127 The index may also be used even if the ORDER BY d 128 DELETE FROM t1; 129 INSERT INTO t1(name) VALUES('abkl'),('bfdjskl'),('cdjkl'),('djiofj'),('efjkl;'),('fjkldsa'),('gljfdka'); 130 SELECT * FROM t1 ORDER BY name DESC, name ASC; 131 name area 132 gljfdka null 133 fjkldsa null 134 efjkl; null 135 djiofj null 136 cdjkl null 137 bfdjskl null 138 abkl null 139 DROP TABLE IF EXISTS t1; 140 CREATE TABLE t1 ( 141 d1 DATE, 142 d2 DATETIME, 143 d3 TIMESTAMP 144 ); 145 INSERT INTO t1() VALUES ('2020-08-08','2020-08-07 00:01:02','2020-08-07 00:01:02.136487'); 146 INSERT INTO t1() VALUES ('2021-09-09','2020-09-09 10:11:02','2020-09-09 10:11:02.136558'); 147 INSERT INTO t1() VALUES ('2021-07-07','2020-07-07 17:17:12','2020-07-07 07:07:05.135582'); 148 INSERT INTO t1() VALUES ('2021-06-06','2020-06-06 21:21:22','2020-06-06 02:21:22.135418'); 149 SELECT * FROM t1 ORDER BY d1 DESC; 150 d1 d2 d3 151 2021-09-09 2020-09-09 10:11:02 2020-09-09 10:11:02 152 2021-07-07 2020-07-07 17:17:12 2020-07-07 07:07:05 153 2021-06-06 2020-06-06 21:21:22 2020-06-06 02:21:22 154 2020-08-08 2020-08-07 00:01:02 2020-08-07 00:01:02 155 SELECT * FROM t1 ORDER BY d1 DESC, d2 ASC, d3 DESC; 156 d1 d2 d3 157 2021-09-09 2020-09-09 10:11:02 2020-09-09 10:11:02 158 2021-07-07 2020-07-07 17:17:12 2020-07-07 07:07:05 159 2021-06-06 2020-06-06 21:21:22 2020-06-06 02:21:22 160 2020-08-08 2020-08-07 00:01:02 2020-08-07 00:01:02 161 SELECT * FROM t1 ORDER BY d2 ASC, d1 ASC, d3 DESC; 162 d1 d2 d3 163 2021-06-06 2020-06-06 21:21:22 2020-06-06 02:21:22 164 2021-07-07 2020-07-07 17:17:12 2020-07-07 07:07:05 165 2020-08-08 2020-08-07 00:01:02 2020-08-07 00:01:02 166 2021-09-09 2020-09-09 10:11:02 2020-09-09 10:11:02 167 SELECT * FROM t1 WHERE d1 BETWEEN '2021-06-06' AND '2021-08-08' ORDER BY d1; 168 d1 d2 d3 169 2021-06-06 2020-06-06 21:21:22 2020-06-06 02:21:22 170 2021-07-07 2020-07-07 17:17:12 2020-07-07 07:07:05 171 DROP TABLE IF EXISTS t1; 172 CREATE TABLE t1( 173 tiny TINYINT NOT NULL, 174 small SMALLINT NOT NULL, 175 int_test INT NOT NULL, 176 big BIGINT NOT NULL 177 ); 178 INSERT INTO t1() VALUES(1, 2, 3, 4),(100, 101, 102, 103),(NULL, NULL, NULL, 204),(64,1,4564,46843); 179 constraint violation: Column 'tiny' cannot be null 180 SELECT * FROM t1 ORDER BY small DESC; 181 tiny small int_test big 182 SELECT * FROM t1 WHERE tiny < 100 ORDER BY big ASC; 183 tiny small int_test big 184 SELECT * FROM t1 ORDER BY tiny ASC, small DESC, int_test ASC, big DESC; 185 tiny small int_test big 186 DROP TABLE IF EXISTS t1; 187 CREATE TABLE t1( 188 float_32 FLOAT, 189 float_64 DOUBLE, 190 d DECIMAL 191 ); 192 INSERT INTO t1() VALUES(0.01, 0.02, 0.03), (0.000001,0.000002,0),(-1,-1.1,-1.2),(0.000003,0.000001,3); 193 SELECT * FROM t1 ORDER BY float_32 ASC, float_64 DESC; 194 float_32 float_64 d 195 -1.0 -1.1 -1 196 1.0E-6 2.0E-6 0 197 3.0E-6 1.0E-6 3 198 0.01 0.02 0 199 SELECT * FROM t1 ORDER BY float_64 DESC; 200 float_32 float_64 d 201 0.01 0.02 0 202 1.0E-6 2.0E-6 0 203 3.0E-6 1.0E-6 3 204 -1.0 -1.1 -1 205 SELECT * FROM t1 ORDER BY d ASC; 206 float_32 float_64 d 207 -1.0 -1.1 -1 208 0.01 0.02 0 209 1.0E-6 2.0E-6 0 210 3.0E-6 1.0E-6 3 211 DROP TABLE IF EXISTS t1; 212 DROP TABLE IF EXISTS t2; 213 DROP TABLE IF EXISTS t3; 214 CREATE TABLE t1( 215 id INT NOT NULL, 216 name CHAR(20) NOT NULL, 217 PRIMARY KEY (id) 218 ); 219 CREATE TABLE t2( 220 id VARCHAR(10) NOT NULL, 221 nation VARCHAR(20) NOT NULL, 222 PRIMARY KEY(id) 223 ); 224 CREATE TABLE t3( 225 nation VARCHAR(20) NOT NULL, 226 city CHAR(20) NOT NULL, 227 GDP FLOAT NOT NULL, 228 PRIMARY KEY(nation) 229 ); 230 INSERT INTO t1() VALUES(1,'ronaldo'), (2,'kante'), (3,'noyer'),(4,'modrici'); 231 INSERT INTO t2() VALUES(1,'Poutanga'), (2,'NA'), (4,'Fenland'); 232 INSERT INTO t3() VALUES('Poutanga','liseber',520135), ('NA','bolando',62102), ('Fenland','yisdilne', 612094); 233 SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.id BETWEEN 1 AND 4+1 ORDER BY name; 234 id name id nation 235 2 kante 2 NA 236 4 modrici 4 Fenland 237 1 ronaldo 1 Poutanga 238 SELECT * FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY t1.id; 239 id name id nation 240 1 ronaldo 1 Poutanga 241 2 kante 2 NA 242 4 modrici 4 Fenland 243 SELECT * FROM t1 JOIN t2 ON t1.id = t2.id JOIN t3 ON t2.nation = t3.nation ORDER BY t2.nation ASC, t3.GDP DESC; 244 id name id nation nation city gdp 245 4 modrici 4 Fenland Fenland yisdilne 612094.0 246 2 kante 2 NA NA bolando 62102.0 247 1 ronaldo 1 Poutanga Poutanga liseber 520135.0 248 SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id RIGHT JOIN t3 ON t2.nation = t3.nation ORDER BY t2.nation ASC, t3.GDP DESC; 249 id name id nation nation city gdp 250 4 modrici 4 Fenland Fenland yisdilne 612094.0 251 2 kante 2 NA NA bolando 62102.0 252 1 ronaldo 1 Poutanga Poutanga liseber 520135.0 253 SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id RIGHT JOIN t3 ON t2.nation = t3.nation ORDER BY t2.nation DESC, t3.GDP DESC; 254 id name id nation nation city gdp 255 1 ronaldo 1 Poutanga Poutanga liseber 520135.0 256 2 kante 2 NA NA bolando 62102.0 257 4 modrici 4 Fenland Fenland yisdilne 612094.0 258 DROP TABLE IF EXISTS t1; 259 DROP TABLE IF EXISTS t2; 260 CREATE TABLE t1( 261 id INT NOT NULL, 262 name CHAR(20) NOT NULL, 263 sex CHAR(4) NOT NULL, 264 PRIMARY KEY (id) 265 ); 266 CREATE TABLE t2( 267 id VARCHAR(10) NOT NULL, 268 name VARCHAR(20) NOT NULL, 269 nation VARCHAR(20) NOT NULL, 270 PRIMARY KEY(id) 271 ); 272 INSERT INTO t1() VALUES(1,'ronaldo','F'), (2,'kante','M'), (3,'noyer','F'),(4,'modrici','M'); 273 INSERT INTO t2() VALUES(1,'ronaldo','American'), (2,'kante','Franch'), (3,'noyer','Germany'),(4,'modrici','UK'); 274 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY name; 275 id name sex 276 2 kante M 277 2 kante Franch 278 4 modrici M 279 4 modrici UK 280 3 noyer F 281 3 noyer Germany 282 1 ronaldo F 283 1 ronaldo American 284 (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY name DESC, id ASC; 285 id name sex 286 1 ronaldo F 287 1 ronaldo American 288 2 kante M 289 3 noyer F 290 4 modrici M 291 2 kante Franch 292 3 noyer Germany 293 4 modrici UK 294 (SELECT * FROM t1) UNION (SELECT * FROM t2 ORDER BY nation) ORDER BY id DESC; 295 id name sex 296 4 modrici M 297 4 modrici UK 298 3 noyer F 299 3 noyer Germany 300 2 kante M 301 2 kante Franch 302 1 ronaldo F 303 1 ronaldo American 304 (SELECT * FROM t1 ORDER BY sex) UNION (SELECT * FROM t2); 305 id name sex 306 1 ronaldo F 307 3 noyer F 308 2 kante M 309 4 modrici M 310 1 ronaldo American 311 2 kante Franch 312 3 noyer Germany 313 4 modrici UK 314 (SELECT * FROM t1 WHERE sex = 'M' ORDER BY sex DESC) 315 UNION 316 (SELECT * FROM t2 WHERE id >= 3 ORDER BY nation ASC) ORDER BY id ASC; 317 id name sex 318 2 kante M 319 3 noyer Germany 320 4 modrici M 321 4 modrici UK 322 (SELECT * FROM t1 WHERE id BETWEEN 1 AND 2 ORDER BY name) 323 UNION ALL 324 (SELECT * FROM t2 WHERE nation BETWEEN 'A' AND 'F' ORDER BY id DESC); 325 id name sex 326 2 kante M 327 1 ronaldo F 328 1 ronaldo American 329 DROP TABLE IF EXISTS t1; 330 DROP TABLE IF EXISTS t2; 331 DROP TABLE IF EXISTS t3; 332 CREATE TABLE t1( 333 id INT NOT NULL, 334 name CHAR(20) NOT NULL, 335 PRIMARY KEY (id) 336 ); 337 CREATE TABLE t2( 338 id VARCHAR(10) NOT NULL, 339 nation VARCHAR(20) NOT NULL, 340 PRIMARY KEY(id) 341 ); 342 CREATE TABLE t3( 343 id VARCHAR(10) NOT NULL, 344 area VARCHAR(20) NOT NULL, 345 PRIMARY KEY(id) 346 ); 347 INSERT INTO t1() VALUES(1,'ronaldo'), (2,'kante'), (3,'noyer'),(4,'modrici'); 348 INSERT INTO t2() VALUES(1,'UK'), (2,'USA'), (3,'RA'),(4,'CN'); 349 INSERT INTO t3() VALUES(1,'EU'), (2,'NA'), (3,'AU'),(4,'AS'); 350 (SELECT * FROM t1) UNION (SELECT * FROM t2 ORDER BY id DESC) UNION ALL (SELECT * FROM t3 ORDER BY area); 351 id name 352 1 ronaldo 353 2 kante 354 3 noyer 355 4 modrici 356 4 CN 357 3 RA 358 2 USA 359 1 UK 360 4 AS 361 3 AU 362 1 EU 363 2 NA 364 ((SELECT * FROM t1 ORDER BY id DESC) UNION (SELECT * FROM t2) UNION ALL (SELECT * FROM t3 ORDER BY area)) ORDER BY id; 365 id name 366 1 ronaldo 367 1 UK 368 1 EU 369 2 kante 370 2 USA 371 2 NA 372 3 noyer 373 3 RA 374 3 AU 375 4 modrici 376 4 CN 377 4 AS 378 (SELECT * FROM t1 ORDER BY name) UNION (SELECT * FROM t2 ORDER BY id) UNION ALL (SELECT * FROM t3 ORDER BY area); 379 id name 380 2 kante 381 4 modrici 382 3 noyer 383 1 ronaldo 384 1 UK 385 2 USA 386 3 RA 387 4 CN 388 4 AS 389 3 AU 390 1 EU 391 2 NA 392 (SELECT * FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY t2.id) 393 UNION 394 (SELECT * FROM t2 RIGHT JOIN t3 ON t2.id = t3.id ORDER BY t3.id DESC); 395 id name id nation 396 1 ronaldo 1 UK 397 2 kante 2 USA 398 3 noyer 3 RA 399 4 modrici 4 CN 400 4 CN 4 AS 401 3 RA 3 AU 402 2 USA 2 NA 403 1 UK 1 EU 404 (SELECT * FROM t1 LEFT JOIN t3 ON t1.id = t3.id ORDER BY t1.id DESC, t3.area ASC) 405 UNION 406 (SELECT * FROM t2 RIGHT JOIN t3 ON t2.id = t3.id ORDER BY t2.nation ASC, t3.id DESC); 407 id name id area 408 4 modrici 4 AS 409 3 noyer 3 AU 410 2 kante 2 NA 411 1 ronaldo 1 EU 412 4 CN 4 AS 413 3 RA 3 AU 414 1 UK 1 EU 415 2 USA 2 NA 416 DROP TABLE IF EXISTS t1; 417 DROP TABLE IF EXISTS t2; 418 CREATE TABLE t1( 419 id INT NOT NULL, 420 d1 CHAR(50) NOT NULL, 421 salary FLOAT NOT NULL, 422 PRIMARY KEY (id) 423 ); 424 CREATE TABLE t2( 425 id INT NOT NULL, 426 name CHAR(50) NOT NULL, 427 sex CHAR(4) NOT NULL, 428 PRIMARY KEY (id) 429 ); 430 INSERT INTO t1() VALUES(1,'2020-01-01',23.6), (2,'2020-01-01',89.6), (3,'2020-01-02',45.6); 431 INSERT INTO t1() VALUES(4,'2020-01-01',66.6), (5,'2020-01-03',17.6), (6,'2020-01-03',123.6); 432 INSERT INTO t2() VALUES(1,'jaca','F'), (2,'mecan','M'),(3,'right','F'),(4,'rodia','M'); 433 INSERT INTO t2() VALUES(5,'hila','F'), (6,'pika','M'); 434 SELECT DATE(d1), MAX(salary) FROM t1 GROUP BY d1 ORDER BY MAX(salary) DESC; 435 date(d1) max(salary) 436 2020-01-03 123.6 437 2020-01-01 89.6 438 2020-01-02 45.6 439 SELECT DATE(d1), MAX(salary) FROM t1 GROUP BY d1 ORDER BY MAX(salary) ASC; 440 date(d1) max(salary) 441 2020-01-02 45.6 442 2020-01-01 89.6 443 2020-01-03 123.6 444 SELECT DATE(d1), MAX(salary) FROM t1 JOIN t2 ON t1.id = t2.id GROUP BY d1 ORDER BY MAX(salary) DESC; 445 date(d1) max(salary) 446 2020-01-03 123.6 447 2020-01-01 89.6 448 2020-01-02 45.6 449 DROP TABLE IF EXISTS t1; 450 CREATE TABLE t1( 451 id INT, 452 name CHAR(20), 453 PRIMARY KEY(id) 454 ); 455 INSERT INTO t1() VALUES(1, 'jacak'), (2, 'tommy'), (3, 'rorgdbs'), (4, NULL); 456 SELECT * FROM t1 ORDER BY name ASC; 457 id name 458 4 null 459 1 jacak 460 3 rorgdbs 461 2 tommy 462 SELECT * FROM t1 ORDER BY name DESC, id ASC; 463 id name 464 2 tommy 465 3 rorgdbs 466 1 jacak 467 4 null 468 SELECT * FROM t1 ORDER BY name ASC NULLS LAST; 469 id name 470 1 jacak 471 3 rorgdbs 472 2 tommy 473 4 null 474 SELECT * FROM t1 ORDER BY name ASC NULLS FIRST; 475 id name 476 4 null 477 1 jacak 478 3 rorgdbs 479 2 tommy 480 SELECT * FROM t1 ORDER BY name ASC NULLS FIRST LAST; 481 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 51 near " LAST;"; 482 DROP TABLE IF EXISTS t1; 483 CREATE TABLE t1( 484 id INT, 485 name CHAR(20), 486 salary FLOAT, 487 dept INT, 488 PRIMARY KEY(id) 489 ); 490 INSERT INTO t1() VALUES(1,'bdkia',133.1,11), (2, 'dodro',983.6,NULL), (3, 'fafeaz',301.5,10), (4, NULL,NULL,13); 491 SELECT * FROM t1 WHERE id > 1 ORDER BY id DESC, salary NULLS FIRST; 492 id name salary dept 493 4 null null 13 494 3 fafeaz 301.5 10 495 2 dodro 983.6 null 496 SELECT * FROM t1 WHERE id BETWEEN 1 AND 4 ORDER BY id DESC, dept NULLS LAST, salary DESC NULLS FIRST; 497 id name salary dept 498 4 null null 13 499 3 fafeaz 301.5 10 500 2 dodro 983.6 null 501 1 bdkia 133.1 11 502 SELECT id,name FROM t1 ORDER BY salary DESC NULLS FIRST, dept DESC NULLS LAST, name ASC NULLS FIRST; 503 id name 504 4 null 505 2 dodro 506 3 fafeaz 507 1 bdkia 508 DROP TABLE IF EXISTS t1; 509 DROP TABLE IF EXISTS t2; 510 CREATE TABLE t1( 511 id INT, 512 name CHAR(20), 513 PRIMARY KEY(id) 514 ); 515 CREATE TABLE t2( 516 id INT, 517 nation CHAR(20), 518 PRIMARY KEY(id) 519 ); 520 INSERT INTO t1() VALUES(1, 'jacak'), (2, 'tommy'), (3, 'roses'), (4, NULL); 521 INSERT INTO t2() VALUES(1, 'US'), (2, 'UK'), (3, NULL), (4, NULL), (5, NULL); 522 SELECT * FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY name DESC NULLS FIRST; 523 id name id nation 524 4 null 4 null 525 2 tommy 2 UK 526 3 roses 3 null 527 1 jacak 1 US 528 SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id ORDER BY name ASC NULLS LAST; 529 id name id nation 530 1 jacak 1 US 531 3 roses 3 null 532 2 tommy 2 UK 533 4 null 4 null 534 SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id ORDER BY name ASC, nation DESC NULLS LAST; 535 id name id nation 536 4 null 4 null 537 null null 5 null 538 1 jacak 1 US 539 3 roses 3 null 540 2 tommy 2 UK 541 SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id ORDER BY name DESC, nation ASC NULLS FIRST; 542 id name id nation 543 2 tommy 2 UK 544 3 roses 3 null 545 1 jacak 1 US 546 4 null 4 null 547 DROP TABLE IF EXISTS t1; 548 DROP TABLE IF EXISTS t2; 549 CREATE TABLE t1( 550 id INT, 551 sex CHAR(20) 552 ); 553 CREATE TABLE t2( 554 id INT, 555 home CHAR(20) 556 ); 557 INSERT INTO t1() VALUES(1, 'F'), (2, 'M'), (NULL, 'M'), (4, NULL), (NULL, NULL); 558 INSERT INTO t2() VALUES(1, 'EU'), (2, 'UK'), (3, NULL), (4, NULL), (5, NULL), (NULL, NULL); 559 SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id ORDER BY sex ASC NULLS LAST; 560 id sex id home 561 1 F 1 EU 562 2 M 2 UK 563 null M null null 564 4 null 4 null 565 null null null null 566 SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id ORDER BY home ASC NULLS FIRST; 567 id sex id home 568 null null 3 null 569 4 null 4 null 570 null null 5 null 571 null null null null 572 1 F 1 EU 573 2 M 2 UK 574 SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id ORDER BY home ASC, sex DESC NULLS FIRST; 575 id sex id home 576 4 null 4 null 577 1 F 1 EU 578 2 M 2 UK